topic Re: 0's Instead of null in Edit Script in QlikView

0's Instead of null in Edit Script

scotly-victor — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: 0's Instead of null in Edit Script

Kushal_Chawda — Fri, 06 May 2016 11:10:11 GMT

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

Re: 0's Instead of null in Edit Script

sunny_talwar — Fri, 06 May 2016 11:13:17 GMT

Check this link out as well

QlikView Addict: Handling Nulls in QlikView

Re: 0's Instead of null in Edit Script

scotly-victor — Fri, 06 May 2016 11:33:00 GMT

Sorry it didn't work

Re: 0's Instead of null in Edit Script

Kushal_Chawda — Fri, 06 May 2016 11:34:57 GMT

Can you share the sample data?

Re: 0's Instead of null in Edit Script

scotly-victor — Fri, 06 May 2016 11:38:27 GMT

if(isnull(Cost),0,Cost)

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

Re: 0's Instead of null in Edit Script

Colin-Albert — Fri, 06 May 2016 11:47:56 GMT

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

Alt(Cost, 0) as Cost

Re: 0's Instead of null in Edit Script

Anonymous — Fri, 06 May 2016 11:53:53 GMT

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

Regards

Re: 0's Instead of null in Edit Script

scotly-victor — Fri, 06 May 2016 12:05:51 GMT

It had changed rest of numeric value as o

Re: 0's Instead of null in Edit Script

sunny_talwar — Fri, 06 May 2016 12:08:20 GMT

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

Re: 0's Instead of null in Edit Script

florentina_doga — Fri, 06 May 2016 13:31:39 GMT

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

Re: 0's Instead of null in Edit Script

swuehl — Fri, 06 May 2016 13:37:21 GMT

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

Re: 0's Instead of null in Edit Script

jonathandienst — Fri, 06 May 2016 14:11:25 GMT

I prefer

Alt(Cost, 0) as Cost

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

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

Re: 0's Instead of null in Edit Script

jonas_rezende — Fri, 06 May 2016 14:11:33 GMT

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!

Re: 0's Instead of null in Edit Script

brijesh1991 — Fri, 06 May 2016 14:28:42 GMT

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

This will set all nulls as 0 in your model.

Re: 0's Instead of null in Edit Script

Colin-Albert — Fri, 06 May 2016 15:23:04 GMT

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.