topic Re: How to include and exclude null values using SET Analysis in QlikView

How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 17:38:16 GMT

Requirements: count distinct ProductID for below condition

1) include only Created status

2) include only when Customer_demand is not null

3) exclude when ImportID is not null

Re: How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 18:08:59 GMT

I am using the IF expression as below and it seems to be working.

Count (DISTINCT if( Status='created' and not isnull(Customer_demand) and isnull (ImportID), ProductID))

But I would like set analysis to do this. with above statement, i am not comfortable with isnull (ImportID) part very much, because it will include all records where ImportID is a null value. Please advise

Re: How to include and exclude null values using SET Analysis

ramoncova06 — Wed, 10 Jun 2015 18:20:06 GMT

this post has a couple of examples

https://community.qlikview.com/docs/DOC-3155/version/7

I normally use nullcount

=avg({<AQ={"=nullcount(TARGET)=0"}>}TARGET)

Re: How to include and exclude null values using SET Analysis

Wed, 10 Jun 2015 18:27:15 GMT

try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={*}-{}>} ProductID)

Re: How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 18:51:13 GMT

Ramon, i did not understand avg and TARGET in your expression.

Uday, your expression did not work. this is what i got:

Re: How to include and exclude null values using SET Analysis

Wed, 10 Jun 2015 19:02:04 GMT

try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={}-{*}>} ProductID)

Re: How to include and exclude null values using SET Analysis

ramoncova06 — Wed, 10 Jun 2015 19:13:43 GMT

that was just an example of what I have used before, try with this expresion

count(distinct{<

status={"created"}, ProductID = {"=nullcount(Customer_demand)=0"} >

<status={"created"}, ProductID = {"=nullcount(ImportID)=0"} > }

ProductID)

Re: How to include and exclude null values using SET Analysis

maxgro — Wed, 10 Jun 2015 20:21:23 GMT

count(distinct{<Status={created}>*<Customer_demand={"*"}>*-<ImportID={"*"}>} ProductID)

Re: How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 21:05:30 GMT

Ramon, i tried your expn; result was 0. so I added minus infront of {"=nullcount(ImportID)=0"} it gave me

it is missing XYZ002. otherwise it would have been the correct ansswer. because the task is to have those records with Created status, include if Cust_demand is NOT null, include if ImportID IS null.

Re: How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 21:11:46 GMT

Massimo, you nailed it. That was the answer. I have broken down the expn to understand your key.

count(distinct{<Status={created}> : this part I get it. Clear

*<Customer_demand={"*"}>* : here, by {'*'}, you mean include all non-null values for Cust_demand..?? what do ** mean at either ends..?

-<ImportID={"*"}>} ProductID) : here I understand that u r doing opposite of Cust_demand, becoz we want to count null values. but there is no ** on either sides. please explain

Re: How to include and exclude null values using SET Analysis

maxgro — Wed, 10 Jun 2015 21:23:00 GMT

intersection

Re: How to include and exclude null values using SET Analysis

maxgro — Wed, 10 Jun 2015 21:32:28 GMT

please close the discussion if you get the answer (mark helpful and correct...., there are already so many open thread); thanks and good qlik

Re: How to include and exclude null values using SET Analysis

Anonymous — Wed, 10 Jun 2015 21:59:37 GMT

massimo,

so if Class 3,6 and 7 is to be excluded, is this the correct expn:

count(distinct{<Status={'created'}> *<Class=-{'3','6','7'}>* <Customer_demand={'*'}> * -<ImportID={'*'}>}ProductID)

this expn is working in this app but when I tried to apply the same logic in my other app, the count was 0. I was curious if you see any thing not right in here,

Thanks