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.

[florentina_doga]

try this

Item cost]:

LOAD

    ItemCode,

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

len(cost) as leng

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

(ooxml, embedded labels, table is Sheet1);

and display leng..

It may be not null, but contain blanks

[swuehl]

Could you elaborate what you mean with 'it didn't work'?

If you check the first table after the LOAD:

[Item cost]:

LOAD

    ItemCode,

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

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

(ooxml, embedded labels, table is Sheet1);

What do you see in field Cost?

You can also calculate your average in a different way

[ItemCost Temp]:

Load ItemCode, Sum(Cost) / Count(ItemCode)  as ItemCost1

RESIDENT [Item cost] group by ItemCode ;

drop table [Item cost];

[jonathandienst]

I prefer

Alt(Cost, 0) as Cost

to

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

(as long as Cost is numeric). Its neater and more compact, but maybe that's just me

[jonas_rezende]

Hi, scotly victor.

My suggestion is to invert the logic and check the result output. Ex.:

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

Once I discovered I had a space in the field and correctly function did'nt catch.

Hope this helps!

[brijesh1991]

Place this variable in your script.
SET SETNULLVALUE=0;

This will set all nulls as 0 in your model.

[Colin-Albert]

A good way to test different functions, is to apply the functions in a straight table chart as an expression with the raw data filed as the dimension.

You add multiple expressions and compare the raw data to the expression result.

This way you can see the result without needing to reload the data for each test.

Then once you have an expression that works and is consistent, you can include that in your script.