Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
d_ankusha
Contributor II

Scripting to compare data from two different sources

Hi All,

I have a table named MAP1 : with fields CITY, STATE, COUNTRY_CODE.

I have another table named INFO: with fields PICKUP_CITY, PICKUP_STATE,PICKUP_COUNTRY, DELIVERY_CITY, DELIVERY_STATE,DELIVERY_COUNTRY.


So I want a script to Get proper State and Country Codes from MAP1 for PICKUP_CITY and DELIVERY_CITY in INFO table.


Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Scripting to compare data from two different sources

4 Replies
Highlighted
MVP
MVP

Re: Scripting to compare data from two different sources

d_ankusha
Contributor II

Re: Scripting to compare data from two different sources

I tried using mapping load, but then I am getting some data issues like: when the value is null for a field, I get ID in place of the null value.

Highlighted
MVP
MVP

Re: Scripting to compare data from two different sources

Use the optional third parameter to the ApplyMap() function to handle cases where the key is not found in the mapping table:

LOAD

     Key,

     ApplyMap('MAPTABLE', Key, 'no entry found in mapping table') as Lookup,

     ...

You can also use Null() function to set NULL for these cases.

Highlighted
Jesh19
Contributor II

Re: Scripting to compare data from two different sources

Try the below code.

State_Map:

Mapping LOAD City, State Resident MAP1;

Country_Map:

Mapping LOAD City, Country Resident MAP1;


Info:

LOAD

PICKUP_CITY,

ApplyMap('State_Map', PICKUP_CITY, 'No City Found') as PICKUP_STATE,

ApplyMap('Country_Map', PICKUP_CITY, 'No City Found') as PICKUP_COUNTRY,

DELIVERY_CITY,

ApplyMap('State_Map', PICKUP_CITY, 'No City Found') as DELIVERY_STATE,

ApplyMap('Country_Map', PICKUP_CITY, 'No City Found') as DELIVERY_COUNTRY