topic Re: Replace nulls after join in QlikView

Replace nulls after join

Mon, 01 Jun 2015 13:15:32 GMT

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(),

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

Re: Replace nulls after join

fvelascog72 — Mon, 01 Jun 2015 13:19:22 GMT

Hi,

Have you tried:

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

Re: Replace nulls after join

Clever_Anjos — Mon, 01 Jun 2015 13:20:10 GMT

Instead of using MAP´s use a

FinalTable:

LOAD

[other fields]

alt(totalUsageX,0) as totalUsageX

RESIDENT yourtable;

drop table yourtable

Re: Replace nulls after join

swuehl — Mon, 01 Jun 2015 13:20:54 GMT

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;

Re: Replace nulls after join

maxgro — Mon, 01 Jun 2015 13:22:06 GMT

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

Re: Replace nulls after join

Mon, 01 Jun 2015 13:25:28 GMT

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