2 Replies Latest reply: Sep 9, 2010 12:42 PM by John De Loach RSS

    Lookup transaction data by key and date out of master data

       

      Hallo everybody,

       

      I'm a beginner with QlickView and I'm facing following issue.

       

      I have a master data table with information which is based on a "valid from" date and I've got transaction details with dates.

      So I have to look up the valid master information based on transaction date.

       

      Here the example

       

      Master data table

      Key valid from date Information (surcharge)

      A 01.01.1900 1.5

      B 10.08.2010 1.8

      B 02.03.2010 1.3

      B 01.01.1900 1.6

      C 07.07.2009 3.7

      C 01.01.1900 2.5

      D …

       

      Transaction data

      Key Date …..

      B 05.05.2010

      C 10.10.2009

      A 03.02.2008

       

      So I'm able to look up the information with the lookup function. But I don't find a way to check the date.

      (e.g. for the first transaction row B 05.05.2010 >> Lookup key B it shoes 1.8 instead of 1.3).

       

      I would need something like this:

      For i = 1 to count(Key)

      Find Key

      Row = Find.Key.Row

      If (TransactionDate < Value(Row, ValidFromDate) then

      Row = Row +1

      Else

      Value(Row, Information)

      End if

      Next i

       

      Has someone got a solution? Please help me. Thank you very much.

      Don't stay with my presumption - I'm a beginner and I don't know it better!

      Regards

      John D.

        • Lookup transaction data by key and date out of master data
          Rakesh Mehta

          Many possibilities out there, but what I would do is create a range.

          So I would add another field in master table, say ValidToDate and initialize it to 12/31/2999. Then I would do intervalmatch() function passing Key as parameter. Something link this

           

          Master:

          Key, ValidFrom, surcharge

          Transaction:

          Key, Date

          Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

          Intervalmatch(Date, Key)

          LOAD ValidFrom, ValidTo, Key Resident Master;

          ////

          And if you do not like synthetic keys, you can left join master table contents back to transactions by using left join. Like:

           

          Master:

          Key, ValidFrom, surcharge

          Transaction:

          Key, Date

          Left join (Master) LOAD Key, date(12/31/2999) as ValidTo Resident Master;

          Left join (Transaction)

          Intervalmatch(Date, Key)

          LOAD ValidFrom, ValidTo, Key Resident Master;

          Left join (Transaction)

          LOAD * RESIDENT Master;

          DROP Table Master.

          ////

          Hope this gives you some ideas & helps you a little.