topic Re: set analysis: fetch null rows in QlikView

set analysis: fetch null rows

Tue, 17 Feb 2015 13:10:59 GMT

Hi,

I have this expression in chart

SUM({<status-={5}>}saleamount)

I have unchecked suppress null values and also unchecked supprse zero and missing values. Can someone please tell me why would this expression still not fetch the null rows?

Shah

Re: set analysis: fetch null rows

Anonymous — Tue, 17 Feb 2015 13:17:26 GMT

sum only works with real values (not NULL)

try rangesum instead

Re: set analysis: fetch null rows

Tue, 17 Feb 2015 13:20:14 GMT

but I do see those null values if I remove the set analysis and then uncheck the suppress zero and suppress missing values.

I have the product on dimensions so I would like to see those products that have null sale amount. We do have those empty sale amount against many products but the chart is not showing those if I have the set analysis. If I remove this set analysis, then it seems to work

Shah

Re: set analysis: fetch null rows

jonathandienst — Tue, 17 Feb 2015 13:23:19 GMT

Set analysis works in the same way as manual selections. In manual selections, you cannot select NULL values, and the same applies to set expressions. You have these options:

Use NULLASVALUE on the field in the load script so that a null behaves like an empty string and is selectable. {<Status={''}>}
Set a Null flag in your load script {<NullFlag={1}>}
Use Sum(If(IsNull(Status) Or Status <> 5, saleamount) (but that may perform poorly on a large data set)

HTH

Jonathan

Edit: the IsNull(Status) is redundant here

Re: set analysis: fetch null rows

Anonymous — Tue, 17 Feb 2015 13:24:16 GMT

did you try if Statement to ao´void set Analysis?

if (Status<> 5, sum(saleamount))

Re: set analysis: fetch null rows

Tue, 17 Feb 2015 13:24:44 GMT

my data set is close to 10 million so I would rather prefer a set analysis

Re: set analysis: fetch null rows

jonathandienst — Tue, 17 Feb 2015 13:28:19 GMT

The first two options in my post use set analysis and should perform well.

Re: set analysis: fetch null rows

Anonymous — Tue, 17 Feb 2015 13:30:51 GMT

sure thats the better solution

maybe this might help: multiply your sum with the Boole value *-1 if Status<>5

sum(Saleamount)*(-1*(Status<>5)

you might build a flag in the script

if (Status<>5,1,0) as StatusFlag

then you could use

sum(Saleamount)*StatusFlag

I had something similiar and it was faster than IF conditions