Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

davidmoz
New 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
Contributor III

Re: Create a new table that match information from 2 tables

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

7 Replies
b_knuever
Contributor III

Re: Create a new table that match information from 2 tables

left join (Data)

Load

*

RESIDENT Location;

Is this what you are looking for?

davidmoz
New Contributor

Re: Create a new table that match information from 2 tables

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
Contributor III

Re: Create a new table that match information from 2 tables

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

b_knuever
Contributor III

Re: Create a new table that match information from 2 tables

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
New Contributor

Re: Create a new table that match information from 2 tables

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
Contributor

Re: Create a new table that match information from 2 tables

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
Contributor III

Re: Create a new table that match information from 2 tables

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.

Community Browser