Sub-selections

valkbart — Mon, 30 May 2011 11:13:53 GMT

Hi,

I have two tables in QlikView that more or less look like this:

Transactions

Columns: Transaction_id

Data:

TransactionRecords

Columns: Transactionrecord_id,Transaction_id,ProductDescription,Discount_id,Quantity,GrossAmount

Data:

1,1,A,NULL,1,4

2,1,E,NULL,1,4

3,1,B,NULL,1,4

4,2,A,1,1,3

5,2,C,NULL,1,4

6,2,D,NULL,1,4

7,3,B,2,1,2

8,3,C,NULL,1,4

9,3,A,1,1,3

In this example I have three transactions:

Transaction #1 has three records and neither of those records have a discount applied to them.

Transaction #2 has three records and the first record has a discount (Discount_id) applied to it.

Transaction #3 has three records and two of those records have a (different) discount applied to them.

I now want to achieve the following:

If i select Discount_id =1, then I want to see a list of all articles that are in a transaction that has one ore more transactionrecords with a Discount_id =1 excluding the ProductDescription that has a Discount applied. So in this case I want Qlikview to return B,C & D.

Can anyone give me a push into the right direction. I'm guessing I should use a P() statement somewhere in my expression, but so far this is the only thing that I've come up with:

sum( {$<Transaction_id = P({1} Discount_id )>} Quantity )

topic Sub-selections in QlikView

Sub-selections