select during load

[#1]

Hello,

I'm new to Qlik Sense and I have the following issue.

I want to link an IPaddress to a physical location (country code).

To achieve this I have retrieved a list of IPaddress ranges that are linked to a country code in an excel file.

The excel file contains the 3 fields :

iprangefrom, iprangeto, countrycode

I have loaded this into a table ( IPAddressCountry ) and added following fields to the load script.

(((SubField(IPFrom,'.',1))*(pow(2,24))) + ((SubField(IPFrom,'.',2))*(pow(2,16))) + ((SubField(IPFrom,'.',3))*(pow(2,8))) + (SubField(IPFrom,'.',4))) as IPFromNumber,

    (((SubField(IPTo,'.',1))*(pow(2,24))) + ((SubField(IPTo,'.',2))*(pow(2,16))) + ((SubField(IPTo,'.',3))*(pow(2,8))) + (SubField(IPTo,'.',4))) as IPToNumber

Next step is to take all the logs I have and load into a table.

So far so good.

What I want to do is the following :

1) Create a new table where to store a distinct of all avaible ip addresses in the log table.

2) Transfrom the IPAddress to a number (IPAddressCalc)

3) Find the IPAddressCalc in the table (IPAddressCountry) to be between IPFromNumber and IPFromTO and return the countrycode as the result.

sub LinkIPAddressToCountryCode

   [tempIPAddressList]:

   Load

    Distinct IPAddress,

    (((SubField(IPAddress,'.',1))*(pow(2,24))) + ((SubField(IPAddress,'.',2))*(pow(2,16))) + ((SubField(IPAddress,'.',3))*(pow(2,8))) + (SubField(IPAddress,'.',4))) as IPaddressCalc,

    (select CountryCode from resident IPAddressCountry where IPAddressCalc >= IPFromNumber and IPAddressCalc <= IPTONumber) as CountryCode

   from Be2NETWebRequestData; 

end sub

What am I doing wrong?

Thanks in advance for your help!

Steven