QSense - Looking for and getting values in another table

[demonioazul]

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:

CustomerName	CustomerAddress	CustomerPostCode	etc
C1	CA1	CPC1	...
C2	CA2	CPC2	...
...	...	...	...
Cn	CAn	CPC3	...

And table 2 looks as follows:

Postal Code	Latitude	Longitude	Location	Province
PC1	Lat1	Long1	City1	Pr
PC2	Lat2	Long2	City2	Pr
...	...	...	...	...
PC970000	Lat970000	Lon970000	City970000	Pr

(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!

[#2]

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.

[danansell42]

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

[demonioazul]

Table1_CanadianPostCodesSample.xlsx‌

Table2_CustomerDataSample.xlsx‌

PostCodeTest.qvf‌

Thanks nhungoatobi‌ 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:

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.

[danansell42 · Accepted solution]

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);

[demonioazul]

Thanks so much, danansell42‌!!

I was so stuck trying with many other ways that were way too complicated...

This works perfectly!