0's Instead of null in Edit Script

[scotly-victor]

Hi Everyone

I wanted to give 0's Instead of null in Edit Script.

Below fieldname cost has null as well as number ,

[Item cost]:

LOAD

    ItemCode,

   if(isnull(Cost),0,Cost) as Cost

FROM [lib://Amazon Data/Item Cost.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ItemCost Temp]:

load ItemCode,avg(Cost) as ItemCost1

RESIDENT [Item cost] group by ItemCode ;

drop table [Item cost];

It didn't work.

[Kushal_Chawda]

try this

[Item cost]:

LOAD

    ItemCode,

if(len(trim(Cost))=0,0,Cost) as Cost

FROM [lib://Amazon Data/Item Cost.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ItemCost Temp]:

load ItemCode,avg(Cost) as ItemCost1

RESIDENT [Item cost] group by ItemCode ;

drop table [Item cost];

[sunny_talwar]

Check this link out as well

QlikView Addict: Handling Nulls in QlikView

[scotly-victor]

Sorry it didn't work

[Kushal_Chawda]

Can you share the sample data?

[scotly-victor]

if(isnull(Cost),0,Cost)

Works in Front End ,However in Edit Script didn't Work

[Colin-Albert]

If Cost is a numeric value then you could use Alt()

Alt(Cost, 0) as Cost

[Anonymous]

It seems that you have some extrange chars on your file. Use RangeAvg.

Regards

[scotly-victor]

It had changed rest of numeric value as o

[sunny_talwar]

Are there joins later on in the script? If there you are doing joins, then you will need to do this nullcheck on the table that is created after the join