2 Replies Latest reply: Mar 5, 2014 12:55 AM by Tom Tierney RSS

    Prior/Previous data

    Tom Tierney

      All,

       

       

      I am extracting data from a wide/long database view. (600/4M). But  for this example assume 4 fields.

       

      Date, Cust ID, Amount1, Amount2

       

      DateCust IDAmount1Amount2PrevAmount1PrevAmount2Other
      201401310011020
      201401310021222
      2014022800114241020
      2014022800216261222

       

      The desired result is shown above. I would like the previous months values (for approx. 10 fields) to be available in the current months dataset. PrevAmount1 and PrevAmount2 in the example above.

       

      Ideally I would like to achieve this in the LOAD statement and not via set analysis. (I have looked at Oleg Troyansky's Current Month/Previous Month post but this uses set analysis albeit in a very efficient way) http://community.qlik.com/message/475041#475041

       

      I have also tried mapping loads but the data set is too large so load times are too long.

       

      Peek/Previous are non runners because the sort order is not fixed.

       

      The PrevAmount1 & PrevAmount2 fields need to be valid when the selected date is changed. (Assume 4 years of data/100K records/rows per month)

       

      Any thoughts?

       

       

      T

        • Re: Prior/Previous data
          Massimo Grossi

          what's the meaning of

          "Peek/Previous are non runners because the sort order is not fixed."

          ?

           

          PrevAmount1 of 20140228  001

          is Amount1 of same Cust ID, previous date

          ordering by Cust ID, Date

           

          or I'm missing something?

            • Re: Prior/Previous data
              Tom Tierney

              My example was a very simplified model. In reality I don't have a customer ID. Instead a staggered hierarchy. So for example I might have Deal level data with 30 attributes and 20 values/amounts. Then Facility level data with 35 attributes and 25 values/amounts. Next Legal Entity level data etc. Usually, but not always, values can be summed up through the hierarchy. There are 10 levels in the hierarchy and being staggered, for a given record, a value/amount might only exist at the top 2-3 levels. So the required sort is just too expensive in terms of the time it adds to the load process. Also, all the data has to be loaded every time because historic data is often enriched with PV and other movements. So little opportunity to cache.

               

              I think what I was after was a general view and suggestions as to how others have solved this type of problem. Your answer was helpful and I have been looking through the forums recently for other ideas. I am leaning towards a SQL solution so that the historic data is delivered in the view.