Qlikview IP --> IP Range mapping

qlikview   intervalmatch   ip   range  

Recently I had a task to map set of ip addresses to country in Qlikview. I've found a nice dataset that can give me this link but the dataset was for ip range to country.

For example:

RangeFrom   , RangeTo      , Country  
1.0.0.1, 1.10.0.1, US  

The solution I've come up with is to use IntervalMatch() function and map the ip list with the range. But the ip addresses are not a normal numeric so I need to convert it to numeric somehow.
My solution is to make the following covertion:

1.0.0.1 --> 001000000100  
192.168.1.1 --> 192168001001  
...

Basically each subnet is converted to a number with leading zeros (if needed) and then all are concatenated in single number.
In QV this can be done with the following script:

num( SubField( ip, '.', 1), 000 ) & num( SubField( ip, '.', 2), 000 ) & num( SubField( ip, '.', 3), 000 ) & num( SubField( ipS, '.', 4), 000 ) as ipNum  

If we apply the same logic and for RangeFrom and RangeTo fields then we will have the ips in number format and IntervalMtch() function can be applied.

The script below demonstrate this logic:

ipList:  
Load  
    num( SubField( IPaddress, '.', 1), 000 ) & num( SubField( IPaddress, '.', 2), 000 ) & num( SubField( IPaddress, '.', 3), 000 ) & num( SubField( IPaddress, '.', 4), 000 )   as ipNum,
    IPaddress
;
Load  
    distinct 
    IPaddress
From  
    [ipList.txt] (txt, codepage is 1251, embedded labels, delimiter is '|', msq)
;

ranges:  
Load  
    num( SubField( RangeFrom, '.', 1), 000 ) & num( SubField( RangeFrom, '.', 2), 000 ) & num( SubField( RangeFrom, '.', 3), 000 ) & num( SubField( RangeFrom, '.', 4), 000 ) as RangeFromNum,
    num( SubField( RangeTo, '.', 1), 000 ) & num( SubField( RangeTo, '.', 2), 000 ) & num( SubField( RangeTo, '.', 3), 000 ) & num( SubField( RangeTo, '.', 4), 000 ) as RangeToNum,    
    RangeFrom,
    RangeTo,
    Country
;
Load  
    @1        as RangeFrom, 
    @2        as RangeTo, 
    @3        as Country
From  
    [ip-country-range.csv] (txt, codepage is 1251, no labels, delimiter is ',', msq)
;

left join ( ipList )  
IntervalMatch ( ipNum ) Load RangeFromNum, RangeToNum Resident ranges;

left join ( ipList )  
Load * Resident ranges;

Drop Table ranges;  

Hope you like it!
Stefan