Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
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];
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
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!
Place this variable in your script.
SET SETNULLVALUE=0;
This will set all nulls as 0 in your model.
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.