0 Replies Latest reply: Jul 17, 2017 7:03 AM by Steven Put RSS

    select during load

    Steven Put

      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