0 Replies Latest reply: May 30, 2011 7:13 AM by Bart van Dodeweerd RSS

    Sub-selections

    Bart van Dodeweerd

      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:

      1. 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 )