Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
d_ankusha
Creator II
Creator 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.


1 Solution

Accepted Solutions
4 Replies
swuehl
MVP
MVP

d_ankusha
Creator II
Creator II
Author

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.

swuehl
MVP
MVP

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.

Jesh19
Creator II
Creator II

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