Load Zero in place of Null in Script

[#1]

Is there a way to load a zero in place of a null in the script? For example I have two tables in Excel, one for Forecast and one for Shipments.

Item	Forecast
Item1	10
Item2	20
Item3	30
Item4	40
Item5	50

Item	Shipments
Item1	5
Item2	25
Item3	30
Item6	20

Item4 and Item5 are not in the Shipments table. Item6 is not in the Forecast table.

When I load both of these tables into one QlikView, the values for those will be null. This however will not work when I use the formula SUM(AGGR(SUM(FABS(Forecast-Shipments)),Item)) as the absolute value cannot calculate when those values are null. I need this formula to calculate forecast accuracy.

Does anyone have any ideas? I've tried using if(isnull(Shipments),0,Shipments) in the script but it does not seem to work.

Thanks,

Justin

[Anonymous]

hi,

in script before load try this.

NullAsValue Item4,Item5;

[swuehl · Accepted solution]

I think you could try something like:

Forecast:

LOAD Item,

     Forecast

FROM

[http://community.qlik.com/thread/37254?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

Shipments:

Outer join (Forecast) LOAD Item,

     Shipments

FROM

[http://community.qlik.com/thread/37254?tstart=0]

(html, codepage is 1252, embedded labels, table is @2);

Result:

NOCONCATENATE LOAD Item,

if(IsNull(Forecast),0,Forecast) as Forecast,

if(IsNull(Shipments),0,Shipments) as Shipments

resident Forecast;

drop table Forecast;

to get a resulting table with Items, Forecast and Shipments and no NULL anymore.

Regards,

Stefan

[#4]

This got me exactly what I was looking for!

Thanks, Stefan!