5 Replies Latest reply: Jun 28, 2016 2:22 PM by Demian Rebollo RSS

    QSense - Looking for and getting values in another table

    Demian Rebollo

      Hi there!

       

       

      I have been browsing the Community but I cannot seem to find what I am looking for.
      I have two Excel files, to make it simpler here, let's say one is from the customer data where I have the companies' Post Code and another table where all Canadian Post Codes are translated into Latitude and Longitude. I need to create a map where I can show the YTD etc with circles of variable size according to their relative value. The map itself is not an issue, I have done plenty of those using only the geodata (lat,long) of the Canadian Provinces' capital cities. I load both tables with no issues but now I need to find the following:

       

      CustomerPostCode from table 1 --> look for that PostCode in table 2 and return back the correct GeoPoint assigned to the customer record.

       

      For example, table 1 looks as follows:

       

      CustomerNameCustomerAddressCustomerPostCodeetc
      C1CA1CPC1...
      C2CA2CPC2...
      ............
      CnCAnCPC3...

       

      And table 2 looks as follows:

       

      Postal CodeLatitudeLongitudeLocationProvince
      PC1Lat1Long1City1Pr
      PC2Lat2Long2City2Pr
      ...............
      PC970000Lat970000Lon970000City970000Pr

      (There are over 970,000 PostCodes in Canada )

       

       

      I am using this load script for table 2 (named zipcodeset):

       

      [zipcodeset]:

      LOAD

        [Postal Code],

          Latitude,

          Longitude,

        GeoMakePoint(Latitude,Longitude) as PhilosGeoKey,

          [Location],

        [Province]

      FROM [lib://Apps/Canadian Post Codes_151014.xlsx]

      (ooxml, embedded labels, table is zipcodeset);

       

      (Location and Province may or may not be used but for this request they are not relevant)

       

      I load first table 2 and then load table 1.

       

      As you may read, I am converting Latitude and Longitude to PhilosGeoKey to allow the map graphing the data point. Also, some Customer records may not have CustomerPostCode...

       

      Now I need to match the Post Code from table 1 to only one in table 2, and append somehow only the individual PhilosGeoKeys to table 1 so I can use table 1's financial data to plot the Canada map.

       

      Any help or hint will be welcome. If needed I can create a small QSense app for further analysis...

      Thanks!

        • Re: QSense - Looking for and getting values in another table
          Nhu Ngo

          Hi,

           

          If you can provide us an example data set, it'll be easier. But from what i see there, you can load the first table like below:

           

          Customer:

          LOAD CustomerName,

                    CustomerAddress,

                    Mid(CustomerPostCode,2,10) As [Postal Code] //look like your Customer Postcode start with C. this need to be the same as the value in Postal code in your 2nd table & column name the same

          FROM Yourfile;

           

          By doing this, those 2 tables will link by Postal Code column.

          • Re: QSense - Looking for and getting values in another table
            Daniel Ansell

            Hi,

             

            Do you require all the other data within zipcodeset to be in the final model?

             

            If not you could go about it in a couple of different ways.

             

            1. If you need multiple columns added to customer table:

            Load customer data then do a left join to bring in the relevant columns from zipcodeset. Just make sure that post code and customer post code are named the same so they actually join.

             

            2. If you only need philosgeokey adding:

            Use applymap function.

            Before loading customer do as follows

             

            //loading all post codes and creating philosgeokey in a temporary table

             

            Zipmap:

            Mapping load

            [Post Code],

            GeoMakePoint (latitude,longitude) as philosgeokey

            From .....

             

            Then load customer table

             

            Customer:

            Load

            Customer name,

            Customer address,

            CustomerPostcode,

            Applymap ('Zipmap',Customer Postcode, 'N/A') as philosgeokey

            From ......

             

            Hope this helps

            Dan

            • Re: QSense - Looking for and getting values in another table
              Demian Rebollo

              Table1_CanadianPostCodesSample.xlsx

              Table2_CustomerDataSample.xlsx

              PostCodeTest.qvf

               

              Thanks nhungo511 and danansell42 for your replies.
              I am adding two sample tables and a sample app.

               

              As explained in my original question, this is the table with customer data and the desired column to be appended:
              Imagw_1.png

              The app loads this first:

               

              [PostalCodeSample]:

              LOAD

                [Postal Code],

                [Latitude],

                [Longitude],

                [Location],

                GeoMakePoint(Latitude,Longitude) as PhilosGeoKey,

                [Province]

              FROM [lib://Apps/Table1_CanadianPostCodesSample.xlsx]

              (ooxml, embedded labels, table is PostalCodeSample);

               

              And then:

               

              [DataSample]:

              LOAD

                [Company Name],

                [Sales Person],

                [Order Value],

                //Many other fields available, but for the sake of simplicity I included only these

                [Company Post Code]

              FROM [lib://Apps/Table2_CustomerDataSample.xlsx]

              (ooxml, embedded labels, table is DataSample);

               

              As danansell42 suggests, after adding the column to Table2 with PhilosGeoKey data, the whole Table1 may be discarded since I do not use it anymore.

               

              Please note that in Table1 Postal Code has no blanks and in Table2 Company Post Code has blanks separating the two parts of a Canadian Post code, thus I am facing also either adding a blank to the whole Table1 Postal Code or eliminating a blank in Table2 Company Post Code. Any suggestions here as well?

               

              I will try the Mapping suggested by danansell42 but I need to solve the blank issue first, otherwise there will be no matches at all when using the Table2 Company Post Code as seed for seaching within Table1 Postal Code.

                • Re: QSense - Looking for and getting values in another table
                  Daniel Ansell

                  Below is the script for both methods.

                  Join seems to run a little quicker than applymap in this instance as i wasn't able to do the geopoint in the mapping load.

                   

                  JOIN OPTION:

                  DataSample:

                  LOAD

                      [Company Name],

                    [Sales Person],

                    [Order Value],

                      //Many other fields available, but for the sake of simplicity I included only these

                      purgechar([Company Post Code],' ') as [Company Post Code]

                  FROM [lib://Folder/Table2_CustomerDataSample.xlsx]

                  (ooxml, embedded labels, table is DataSample);

                   

                   

                  left join (DataSample)

                   

                   

                  PostalCodeSample:

                  LOAD

                      [Postal Code] as [Company Post Code],

                    GeoMakePoint(Latitude,Longitude) as PhilosGeoKey

                   

                  FROM [lib://Folder/Table1_CanadianPostCodesSample.xlsx]

                  (ooxml, embedded labels, table is PostalCodeSample);

                   

                   

                  APPLYMAP OPTION:

                   

                  PostalCodeSample:

                  LOAD

                      [Postal Code],

                    [Latitude],

                    [Longitude],

                    [Location],

                      GeoMakePoint(Latitude,Longitude) as PhilosGeoKey,

                    [Province]

                  FROM [lib://Folder/Table1_CanadianPostCodesSample.xlsx]

                  (ooxml, embedded labels, table is PostalCodeSample);

                   

                   

                  Zipmap:

                  Mapping load

                  [Postal Code],

                  PhilosGeoKey

                   

                   

                  resident PostalCodeSample;

                  Drop Table PostalCodeSample;

                   

                   

                  DataSample:

                  LOAD

                      [Company Name],

                    [Sales Person],

                    [Order Value],

                      //Many other fields available, but for the sake of simplicity I included only these

                    Applymap ('Zipmap',purgechar([Company Post Code],' '), 'N/A') as philosgeokey,

                      [Company Post Code]

                  FROM [lib://Folder/Table2_CustomerDataSample.xlsx]

                  (ooxml, embedded labels, table is DataSample);