Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bkhashfeh
New Contributor II

Expression to get List1\List2

Dears,

I'm new to Qlik sense, I faced this case, and I need your help please.

I have the following table with data:

ID ,  SRC

------------

111,1

111,1

111,1

123,1

134,1

678,1

123,2

123,2

134,2

999,2

lets name the above table 'TBL'.

I need to build an expression to get the following

result1= 2 which means count(distinct TBL.ID) where SRC=1 and ID not in(TBL.ID where SRC=2),

only ID=111,678 has SRC=1 and not exist for SRC=2

result2= 1 which mean count(distinct TBL.ID) : SRC=2 and ID not in(TBL.ID where SRC=1)

only ID=999 has SRC=2 and ID value not exist in SRC=1

Note:

I need this result regardless of the current selection.

Also, can I build table chart and use one column to show the result IDs without count aggregation function?

Thanks in advanced

Bassel Kh.

Tags (1)
1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Expression to get List1\List2

for unique in SRC 1

count({1<SRC = {1} , ID = e({<SRC = {2}>}ID)>}DISTINCT  ID)


for unique in SRC 2

count({1<SRC = {2} , ID = e({<SRC = {1}>}ID)>}DISTINCT  ID)

5 Replies
vinieme12
Esteemed Contributor II

Re: Expression to get List1\List2

for unique in SRC 1

count({1<SRC = {1} , ID = e({<SRC = {2}>}ID)>}DISTINCT  ID)


for unique in SRC 2

count({1<SRC = {2} , ID = e({<SRC = {1}>}ID)>}DISTINCT  ID)

vinieme12
Esteemed Contributor II

Re: Expression to get List1\List2

if your problem is resolved, please close the thread

bkhashfeh
New Contributor II

Re: Expression to get List1\List2

Thanks a lot, it worked

I didn't know about e(), and p().

Can I show the result in table chart without count? or this is just for aggregation function

vinieme12
Esteemed Contributor II

Re: Expression to get List1\List2

use

only({1<SRC = {1} , ID = e({<SRC = {2}>}ID)>}  ID)

or in a textbox

=concat( Distinct  AGGR(only({1<SRC = {1} , ID = e({<SRC = {2}>}ID)>}  ID),ID,SRC),';')

bkhashfeh
New Contributor II

Re: Expression to get List1\List2

It worked that you