Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can use a MAPPING table approach:
You can use a MAPPING table approach:
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:
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.
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