Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
davidmoz
Contributor
Contributor

Create a new table that match information from 2 tables

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.


Captura.PNG


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.

1 Solution

Accepted Solutions
mikaelsc
Specialist
Specialist

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...

View solution in original post

7 Replies
bramkn
Partner
Partner

left join (Data)

Load

*

RESIDENT Location;

Is this what you are looking for?

davidmoz
Contributor
Contributor
Author

Bram, thank you for your response.

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

Captura.PNG

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

I'm missing something

mikaelsc
Specialist
Specialist

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
Partner
Partner

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.

davidmoz
Contributor
Contributor
Author

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
Creator
Creator

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
Specialist
Specialist

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.