Discussion board where members can get started with Qlik Sense.
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.
Go to Solution.
You can use a MAPPING table approach:
Mapping … and not the geographical kind
Don't join - use Applymap instead
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.
Use the optional third parameter to the ApplyMap() function to handle cases where the key is not found in the mapping table:
ApplyMap('MAPTABLE', Key, 'no entry found in mapping table') as Lookup,
You can also use Null() function to set NULL for these cases.
Try the below code.
Mapping LOAD City, State Resident MAP1;
Mapping LOAD City, Country Resident MAP1;
ApplyMap('State_Map', PICKUP_CITY, 'No City Found') as PICKUP_STATE,
ApplyMap('Country_Map', PICKUP_CITY, 'No City Found') as PICKUP_COUNTRY,
ApplyMap('State_Map', PICKUP_CITY, 'No City Found') as DELIVERY_STATE,
ApplyMap('Country_Map', PICKUP_CITY, 'No City Found') as DELIVERY_COUNTRY