Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have 3 tables that are basically identical except for the type of "pick" transaction. I used concatenate load to combine these tables. I have a pick(match()) statement to clean up the names of the equip_code (so creating an equip_code_desc)
Pick(match(equip_code, '1', '2', '3'), 'a', 'b', 'c') as equip_code_desc
I've put this formula into each of the load statements for the 3 tables. It's working as expected. However, I have null values in the equip_code. Typically, I'd just use if(isnull()) to allow me to filter by the nulls. I tried
If(isnull(equip_code),'null',Pick(match(equip_code, '1', '2', '3'), 'a', 'b', 'c')) as equip_code_desc
but that didn't work.
I tried
If(Equip_code LIKE '','null',Pick(match(equip_code, '1', '2', '3'), 'a', 'b', 'c')) as equip_code_desc
which also did not work.
Then I tried preceding loads, but that didn't work either. Maybe because I used load * for all of the preceding loads? Do I need to use CONCATENATE LOAD * for the preceding loads in from of the concatenated tables?
How do I handle null values in a pick(match()) statement?
Coalesce(Pick(match(equip_code, '1', '2', '3'), 'a', 'b', 'c'),'null')
or
Pick(wildmatch(equip_code, '1', '2', '3','*'), 'a', 'b', 'c','null')
Should work
Hi! Try:
If(len(trim(Equip_code)) = 0,'null',Pick(match(equip_code, '1', '2', '3'), 'a', 'b', 'c')) as equip_code_desc
I realized that the null values I was seeing was caused by another table I had joined to. I've solved that and no longer have nulls. I don't know that I can accept either response as solution since I didn't end up using any of the suggested functions so I don't know which one would work best. But I'm glad to know about WildMatch function going forward. Thanks for your help!