Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
AK9934
Contributor II
Contributor II

Need to get the distinct count of sk from both expressions for only matches sks

1) get the distinct count of sk where type= AG

2) get the distinct count of sk where type= AGH

3) get the sk which are in step 1 not in step 2 

4) calculate distinct count of sk from step 3.

 

Please provide me the solution how to achieve this using set expression.

Labels (3)
7 Replies
lennart_mo
Creator II
Creator II

Hi @AK9934,

Could you please elaborate on your data structure?
Are the types in step 1 and step 2 in the different fields or can one sk have multiple types?

AK9934
Contributor II
Contributor II
Author

Hi @lennart_mo,

fact table contains Type and sk Columns.

Type is a column have only 2 values AG and AGH.

one sk may have both AG and AGH values.

I need to count of district of sk values which are common in both.

 

Kushal_Chawda

@AK9934  try below expression\

Count({<SK=p({1<Type={'AGH'}>})*p({1<Type={'AG'}>})>}distinct SK)

AK9934
Contributor II
Contributor II
Author

Not working the above expression.

Kushal_Chawda

@AK9934  This should work. Make sure that you are using actual field name in your expression. If it still doesn't work, please share a sample data with expected output

AK9934
Contributor II
Contributor II
Author

Hi,

For example if we take these 2 values in Excel like Column A has AG values and Column B has AGH Values, if we apply vlookup as vlookup(B2, A2:A77,1,0) then getting some are matches and other as N/A in Excel and  if we filter N/A in the lookup column then what are the values showing under column A I need in qliksense.

Can anyone please help?

simsa
Contributor III
Contributor III

Hi @AK9934,

I have attached the sample data in snippet that I have used to validate your requirement.

Here is the formula I have used, hope it works for you.

SUM(IF(AGGR(CONCAT(DISTINCT Category),ID)='A',1,0))

 

In your case, you can use:

SUM(IF(AGGR(CONCAT(DISTINCT type),sk)='AG',1,0))