topic Re: Left Join with a list of values in Connectivity & Data Prep

Left Join with a list of values

takama13 — Fri, 13 Apr 2018 08:45:51 GMT

Hi all,

I have an excel file with my data.

This data contain, inter alia, a field with cities.

I create a map showing some data of my cities.

But some cities are not recognized by Qlik Sense (maybe a question of name syntax).

I retrieved from internet an excel file with the list of world cities, with their longitude and latitude.

This file is built like this :

City Name / Alternative names / Longitude / Latitude

The column 'Alternative names' is a list of names separeted by ','

I would like to left join this table to my excel file but I don't know how to proceed when my city is listed in the 'Alternative names' column.

Example :

In data file, I have the city Khor Fakkan.

In the world cities file, this city is listed as :

City Name / Alternative names / Longitude / Latitude

Khawr Fakkān / Khawr al Fakkān,Khor Fakhan,Khor Fakkan,Khor Fakkān,Khor al Fakhan / 56.34199 / 25.33132

How should I proceed left join this table ?

Thanks in advance for your help.

Re: Left Join with a list of values

petter — Fri, 13 Apr 2018 14:18:40 GMT

I would rather do a MAPPING LOAD and then use the ApplyMap() function. It should be simpler and quicker than a left join:

ALTERNATIVE_CITY_NAMES:
MAPPING
LOAD 
  SubField( [City Name] & ',' & [Alternative names] ,',' ) AS CityName,
  Longitude & ',' & Latitude AS LonLat    // Mapping table can only have two columns - so have to join these
INLINE [
City Name /  Alternative names / Longitude / Latitude
Khawr Fakkān / Khawr al Fakkān,Khor Fakhan,Khor Fakkan,Khor Fakkān,Khor al Fakhan / 56.34199 / 25.33132
Göteborg / Gøteborg, Gothenburg / 11.974560 / 57.708870
München / Munich / 48.137154 / 11.576124
] (delimiter is '/');


CITIES:
LOAD
  CityName,
  SubField(LonLat,',',1) AS Longitude,
  SubField(LonLat,',',2) AS Latitude;
LOAD
  CityName,
  ApplyMap('ALTERNATIVE_CITY_NAMES',CityName) AS LonLat
INLINE [
CityName
Khawr al Fakkān
Munich
Gøteborg
];

Re: Left Join with a list of values

petter — Fri, 13 Apr 2018 14:47:43 GMT

It is quite easy to do a left join too and it would look like this:

CITIES:
LOAD
  CityName
INLINE [
CityName
Khawr al Fakkān
Munich
Gøteborg
];


LEFT JOIN


ALTERNATIVE_CITY_NAMES:
LOAD 
  SubField( [City Name] & ',' & [Alternative names] ,',' ) AS CityName,
  Longitude,
  Latitude
INLINE [
City Name /  Alternative names / Longitude / Latitude
Khawr Fakkān / Khawr al Fakkān,Khor Fakhan,Khor Fakkan,Khor Fakkān,Khor al Fakhan / 56.34199 / 25.33132
Göteborg / Gøteborg, Gothenburg / 11.974560 / 57.708870
München / Munich / 48.137154 / 11.576124
] (delimiter is '/');

Lines 4-9 would be replace by a FROM statement - and you would probably have that done with the lines 18-23.

Re: Left Join with a list of values

takama13 — Fri, 20 Apr 2018 08:05:14 GMT

Petter,

Working perfectly.

Was not working at the beginning until I found that JOIN is case sensitive.

I solved this issue with the UPPER function.

Thanks

Re: Left Join with a list of values

petter — Fri, 20 Apr 2018 20:36:07 GMT

If this answered your question please mark the answer as "answered"

Thanks