Create a new table that match information from 2 tables

[Anonymous]

Hello,

I am trying to make a map using 2 different tables.

The Data table has the provider info and data.
The Location table is a big one which have the location information, including latitude and longitude for the GeoMakePoint function, of every city in Europe. This one is used as a support for the Data table.

I want to have a new table with all the Data table information and ONLY all the location information that match Postal Code.

Then I could use this table to place the providers on my map.

If you think there is a better way than creating a new table I’ll enjoyed it.

I have tried many things but the map always displayed all the location instead of the matched ones.

Thank you for your help.

[bramkn]

left join (Data)

Load

*

RESIDENT Location;

Is this what you are looking for?

[Anonymous]

Bram, thank you for your response.

I already tried this but I still have this problem...

I would like to display only the Data referenced points and not all the Location table.

I'm missing something

[mikaelsc · Accepted solution]

ensure to convert both postal codes to the same type of data before joining. (text(trim("Postal Code")) for example...

it could give some strange results sometimes otherwise.

or use a where exists() clause, but again ensuring you compare the same type of fields..

last thing: State ISO is also available in both table. even if it is the same info, only keep it in one (locations?) table (otherwise, again, risk of duplicates when joining)

just guessing what may be going wrong...

[bramkn]

Can you share your code/app? As there is probably something else that is causing this. The left join should leave you with the data requested.

[Anonymous]

Mikael, Bram, thank you for your response.

Both of you helped me and now it work well.

This is how I processed :

[Location_Data]:

left join (Data)

Load GeoMakePoint([Latitude], [Longitude]) AS [Longitude_Latitude],

*

RESIDENT Location where Exists ([Postal-Code]);

[campbellr]

Another option is to use applymap() function to add the lat and long to your data. or simply have the locations load as a separate table and have the map only show locations where there is a value in your data

Ron

[mikaelsc]

Also, just adding a measure of your Data in your Map should allow you keep all locations in your model, but only showing the ones with data on your map.