2 Replies Latest reply: Sep 5, 2012 4:44 PM by chraaroe RSS

    Registering changes in a dataset and flagging the rows

      Hi all,

       

      I'm trying to get into this whole exciting QlikView thing and have been tasked with a, presumably, simple task which I can't seem to wrap my head around and I'm hoping for some pointers from you guys and girls.

       

      First of, I'm dealing with a sales system where I'm fetching all the orderlines on a daily basis. The task is to register which orderlines have changed since the last reload and flag these rows? Sounds simple enough - well I don't

       

      Here's a very simplistic example:

       

      On day one, I have the following dataset

       

      OrderIDAmountFrequencyPrice
      10013210
      1002838
      1003529

       

      Now, when I, the following day, fetch the dataset again, it may look like this:

      OrderIDAmountFrequencyPrice
      10013210
      1002638
      1003529

      On the second orderline, OrderID 1002, one of the sales people have changed the existing order from an amount of 8 to 6. My wish is to somehow flag this row as HAS_CHANGED so I can use it for any sort of filtering in the application.

       

      I will, ofcourse, add that it is a very simplified example - in the application, there are many more elements which are subject to change but I feel that if I only get my mind lined in with this problem, I'll be on my merry way

       

      Any good advice on how to do this? Please let me know if you have any questions and thank you for your time.

       

      Best regards

      Christian

        • Re: Registering changes in a dataset and flagging the rows
          Dave Riley

          Hi Christian,

           

          For some reason I cannot upload my test document, but if you copy and paste this code into the load script and then run it you can then create a table box object.  When you run it again you should see the DataAnalysis field report no changes.  If you then change data in the INLINE load section and re-run it, the line with the change should report changes.

           

           

           

          //Load previous data
          Qualify *;
          If FileSize('OrderLines.qvd')>0 then
          OrderLines_Prev:
          LOAD OrderID, RowCheck FROM OrderLines.qvd (qvd);
          else
          OrderLines_Prev:
          LOAD '' as OrderID, '' as RowCheck
          AutoGenerate 1;
          End If;
          Unqualify *;

           

          //Load current data (this example is inline data load)
          OrderLines:
          Load * Inline [
          OrderID, Amount, Frequency, Price
          1001,  3,  2,   10
          1002,  6,  3,   8
          1003,  5,  2,   9];

           

          //Create a rowcheck on the columns you want to inspect to allow comparison
          Left Join (OrderLines)
          LOAD OrderID, Amount & '-' & Frequency & '-' & Price as RowCheck
          Resident OrderLines;

           

          //Now compare the new data with the old data
          Left Join (OrderLines)
          LOAD
          OrderLines_Prev.OrderID as OrderID,
          OrderLines_Prev.RowCheck
          Resident OrderLines_Prev;

           

          //Previous data not now required
          DROP Table OrderLines_Prev;

           

          //Now compare the data in the OrderLines table
          Left Join (OrderLines)
          LOAD OrderID,
          If(RowCheck=OrderLines_Prev.RowCheck,'no change',
            If(IsNull(OrderLines_Prev.RowCheck),'new','changes')) as DataAnalysis
          resident OrderLines;

           

          //Store the data into QVD to reload next time
          STORE OrderLines into OrderLines.qvd (qvd);

           

           

           

          Hopefully this will give you some ideas.

           

          flipside