Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Tags (2)
5 Replies
MVP
MVP

Re: Selecting two values (distinct sum)

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

Re: Selecting two values (distinct sum)

Hi Sunny,


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

MVP
MVP

Re: Selecting two values (distinct sum)

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

Re: Selecting two values (distinct sum)

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
New Contributor III

Re: Selecting two values (distinct sum)

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.

Community Browser