Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selecting two values (distinct sum)

Hi there,


I am trying to make a set analysis for a bit of SQL code which I have done which shows the correct number of distinct customers which have both products.

select count(distinct ID) as Number

from table1 x

where

X.Year= '2016/05' and

(X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 ) > 0  and

( X.Product_2+ X.Product_2+ X.Product_2) > 0 and

( X.Product_3+ X.Product_3+ X.Product_3) = 0 and

( X.Product_4+ X.Product_4) = 0


(If these two products added together are greater than 0 then count as distinct 1 on ID).


The set analysis I have been trying to work with is the following;


COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>}) + P({<Product_1={">0"}>})

+P ({<Product_2={">0"}>}) +P ({<Product_2={">0"}>}) +P ({<Product_2={">0"}>})

>}  DISTINCT ID)

This gives an incorrect number, should I be excluding the other tables also to get the correct figure, just like my SQL?

5 Replies
sunny_talwar

Would you be able to expand on the usage of repeating the use of X.Product_1 on first row and repeating products on other rows?

select count(distinct ID) as Number

from table1 x

where

X.Year= '2016/05' and

(X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 ) > 0  and

( X.Product_2+ X.Product_2+ X.Product_2) > 0

( X.Product_3+ X.Product_3+ X.Product_3) = 0

( X.Product_4+ X.Product_4) = 0

Not applicable
Author

Hi Sunny,


The products have their own individual name, product one was just an example (category type).

sunny_talwar

I am not sure what you are trying to do, would you be able to share few rows of sample (dummy) data with expected output based on the data?

Not applicable
Author

YearIDProduct 1 - AppleProduct 1 - OrangeProduct 1 - PearProduct 2 - CarrotProduct 2 - PotatoProduct 2 -OnionProduct 2 - Celery
2016/05999990100110
2016/05100000000110

Here is an example of dummy data.

Therefore every time product 1 & 2 has at least one true value (1) then it counts ID as distinct 1.

The original code excludes the other two columns on the table to give the correct output.

isaaclin
Contributor III
Contributor III

I have an idea, add flag columns in your load statment

such as if (X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 + X.Product_1 ) > 0, 1,  0) as Product1Flag,

Do the same to

( X.Product_2+ X.Product_2+ X.Product_2) > 0

( X.Product_3+ X.Product_3+ X.Product_3) = 0

( X.Product_4+ X.Product_4) = 0

then you only need to filter the flag columns  to get what you need.