Replace nulls after join

[#1]

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

[fvelascog72]

Hi,

Have you tried:

if(isnull(totalUsage),0,totalUsage) as totalUsageX

[Clever_Anjos]

Instead of using MAP´s use a

FinalTable:

LOAD

[other fields]

alt(totalUsageX,0) as totalUsageX

RESIDENT yourtable;

drop table yourtable

[swuehl]

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;

[maxgro]

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 .......;

[#6]

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