I'm having some trouble applying a null mapping. I'm trying to map a bunch of global id's from one table (tmp table) to some Marketing client Id's in another table (mapping table), and then where there is no corresponding global id in the mapping table, apply a nullmap.
So far, my code looks like the following:
Mapping:
//Default mapping for unallocated clients (date) Marketing_NullMap: MAPPING LOAD null(), REPLACEMENT; SQL select REPLACEMENT from e_unallocated_lookup
Applying the mapping:
LEFT JOIN(TRADES_HISTORY_CRD_DETAILED_TMP) IntervalMatch(TRADE_DATE, GLOBAL_CLIENT_ID) LOAD EFFECTIVE_FROM, EFFECTIVE_TO, M_TRADING_ACRONYM AS GLOBAL_CLIENT_ID RESIDENT MAPPING_DATA;
LEFT JOIN (TRADES_HISTORY_CRD_DETAILED_TMP) LOAD EFFECTIVE_FROM, EFFECTIVE_TO, M_TRADING_ACRONYM AS GLOBAL_CLIENT_ID, applymap('Marketing_NullMap', MARKETING_CLIENT_ID, 'APPLIED') AS MARKETING_CLIENT_ID RESIDENT MAPPING_DATA;
You'll see that there is a default mapping of 'APPLIED', which is getting applied, but the nulls are still showing up. I also tried using
MAP MARKETING_CLIENT_ID USING Marketing_NullMap before Market client ID gets loaded, but to no avail. Any ideas?
(Apologies for not using CODE tags, but they were messing up to the point it was entirely unreadable no matter what I did.)
*I Think*, the problem is that you don't only have "nulls", but you might also have "missing" values. The end result is similar - you get null values, but the process is different. When you apply the Map during "left join", my guess is that those null values simply don't exist at that point. The reason you are seeing nulls at the end is because missing values get padded with nulls after the left join is performed.
I suggest reloading the final table once again and applying the map there:
TRADES_HISTORY_CRD_DETAILED: load *, applymap('Marketing_NullMap', MARKETING_CLIENT_ID, 'APPLIED') AS MARKETING_CLIENT_ID_2 resident TRADES_HISTORY_CRD_DETAILED_TMP; drop table TRADES_HISTORY_CRD_DETAILED_TMP;
*I Think*, the problem is that you don't only have "nulls", but you might also have "missing" values. The end result is similar - you get null values, but the process is different. When you apply the Map during "left join", my guess is that those null values simply don't exist at that point. The reason you are seeing nulls at the end is because missing values get padded with nulls after the left join is performed.
I suggest reloading the final table once again and applying the map there:
TRADES_HISTORY_CRD_DETAILED: load *, applymap('Marketing_NullMap', MARKETING_CLIENT_ID, 'APPLIED') AS MARKETING_CLIENT_ID_2 resident TRADES_HISTORY_CRD_DETAILED_TMP; drop table TRADES_HISTORY_CRD_DETAILED_TMP;
Oleg, thats pretty much the conclusion that I came to after a lot of poking around, I think you're right because that solution does work. It does seem like an awful lot of overhead for something that seemed like it would be so easy, but I can't really see any other way around it. Thanks for your help, I thought I was just using mappings completely wrong.
