Hi,
I have two tables in QlikView that more or less look like this:
Transactions
Columns: Transaction_id
Data:
1
2
3
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 )