Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I cannot seem to replace some nulls after a join, I want them to be zero, but they remain null. This is part of the code.
All_metals contains all different metals we have (metalStyle & metalColor combination), while Produced_aggregated_3 contains the amount we have produced, but a row only exists if we have produced any of this metal at all.
Map_Null1:
MAPPING LOAD
Null(),
0
Autogenerate 1;
MAP totalUsageX USING Map_Null1;
OUTER JOIN (All_metals)
LOAD
metalStyle,
metalColor,
totalUsage as totalUsageX
Resident Produced_aggregated_3;
Store All_metals into xyz2.qvd;
Rename table All_metals to Produced_aggregated_final;
INNER JOIN (Produced_aggregated_final) LOAD * RESIDENT Produced_aggregated_final;
If I open the xyz2.qvd in a new file, it shows:
metalStyle metalColor totalUsageX
A1 red 100
A1 blue -
B1 red 200
So the null value is still in the table, but I want the second row to display a zero in totalUsageX for further calculations.
Anybody have an idea on how to fix this?
Thanks in advance,
Florian
Hi,
Have you tried:
if(isnull(totalUsage),0,totalUsage) as totalUsageX
Instead of using MAP´s use a
FinalTable:
LOAD
[other fields]
alt(totalUsageX,0) as totalUsageX
RESIDENT yourtable;
drop table yourtable
Do a resident load of your final table (with noconcatenate prefix) and replace the NULL with zero.
NewFinal:
NOCONCATENATE
LOAD
metalStyle,
metalColor,
if(len(trim(totalUsageX)),totalUsageX,0) as totalUsageX
Resident Final;
drop table Final;
before store you can do a resident load and test (and replace) null
rename table All_metals to tmp;
All_metals:
noconcatenate load ....., if(len(trim(totalUsageX))=0, 0, totalUsageX) as totalUsageX resident tmp;
drop table tmp;
store .......;
Thank you all very much for the quick responses. I can definitively work with this. One follow up question: Is there also a reason why MAP does not work in my case?
Thanks.
Florian