/ qlikview

Qlikview IP --> IP Range mapping

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