topic Re: how to solve this with Set Analysis and If statement in QlikView

how to solve this with Set Analysis and If statement

Anonymous — Mon, 24 Aug 2015 18:11:23 GMT

count those ProductIDs where status is created and distinct count of TransactionID =2. so in my below eg, I should get only XYZ002 with

TransID 10002 & 10003. I tried

count({<Status={CREATED}, ProductID={'=count(DISTINCT TransactionID)=2'}>}Distinct

ProductID) but it is not working

kaushiknsolanki — Mon, 24 Aug 2015 18:26:04 GMT

Try this,

=Count({<Status = {"Created"},ProductId = {"=count({<Status = {'Created'}>}Distinct TrxID)=2"}>}Distinct ProductId)

Regards,

Kaushik Solanki

swuehl — Mon, 24 Aug 2015 18:27:47 GMT

petter — Mon, 24 Aug 2015 18:28:12 GMT

How to solve it is a bit dependent on how you want to display your result - which you haven't told us.

I assume you want the result in a Straight Table and then it is quite simple to display the ProductID which

match your criteria like this:

Add one single dimension which is ProductID and one single expression which is

Count( { < Status = { 'CREATED' } > } TransactionID ) = 2

Then you will have a two column straight table with a single row - XYZ002.

If you want a list of ProductIDs in a text box you could use this expression:

=Concat( Aggr( If( $(A1) , ProductID ) , ProductID ) , Chr(10) )

Kushal_Chawda — Mon, 24 Aug 2015 18:28:53 GMT

try,

Count({<ProductId = {"=count({<Status = {'CREATED'}>}Distinct TrasactionID)=2"}>}Distinct ProductId)

maxgro — Mon, 24 Aug 2015 18:33:06 GMT

count({<Status={CREATED}, ProductID={"=count({$ <Status={CREATED}>} DISTINCT TransactionID)=2"}>}Distinct ProductID)

Anonymous — Mon, 24 Aug 2015 23:48:44 GMT

Thanks everyone. Special Thanks goes to sweuhl for being the first responder to be correct.