Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Scripting to compare data from two different sources

4 Replies
MVP
MVP

Re: Scripting to compare data from two different sources

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

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.

jeshwanth19
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