topic Assigning IP-Address to IP-Range in QlikView

Assigning IP-Address to IP-Range

Tue, 13 Jul 2010 09:13:41 GMT

Hello,

I'm not working with QlikView that long so I apologize if this question has been already answered in another post but i didn't really know what to search for in the forum.

My problem is as follows:

I have got a table called "logfile" and another table called "location". in the logfile there is some information about the users connection.

On the one hand the user can be identified with his IP-address in the log file

on the other hand you can assign an IP address to a IP-range to distinguish between the locations

due to the fact that this should not be a user specific but a location specific reporting, I tried to connect the fields IP-range and IP-address using the textbetween function but this does not work. has anybody an idea how to solve that problem?

thanking you in anticipation

Assigning IP-Address to IP-Range

Tue, 13 Jul 2010 09:59:07 GMT

I did it in this way:

To do ip range checks you have to convert every ip to numeric format. Just removing the dots won't make it decimal numeric so make sure you do it the correct way.

I created a NumericIp field like this in QV script:

Load subfield(ip,'.',1) << 24 + subfield(ip,'.',2) << 16 + subfield(ip,'.',3) << 8 + subfield(ip,'.',4) << 0 as NumericIp;

Use http://www.allredroster.com/iptodec.htm to validate your numeric ip's. For example, the ip 1.1.1.1 should equal 16843009 in numeric format.

Now you are ready to match the ip with your locations file that contains ip ranges and locations. My ip ranges file looks like this:

"2.6.190.56","2.6.190.63","33996344","33996351","Location 1"

"4.17.135.32","4.17.135.63","68257568","68257599","Location 2"

... and so on.

As you can see, I have pre-calculated the numeric ip's but you can make you conversion in the script when you load the locations file if you prefer that. Load the the locations file into a table called IpRanges and name the fields IpRangeStart, IpRangeEnd and IpLocation

Finally, you map the location for a specific ip by using the interval match:

Intervals:
intervalmatch ([NumericIp]) Load [IpRangeStart], [IpRangeEnd] Resident IpRanges;

I have posted an example qvw here: http://community.qlik.com/media/p/121093.aspx

Assigning IP-Address to IP-Range

Tue, 13 Jul 2010 10:49:46 GMT

Works great! Nothing left to say. Tank you