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




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


      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


        • 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
          LOAD OrderID, RowCheck FROM OrderLines.qvd (qvd);
          LOAD '' as OrderID, '' as RowCheck
          AutoGenerate 1;
          End If;
          Unqualify *;


          //Load current data (this example is inline data load)
          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)
          OrderLines_Prev.OrderID as OrderID,
          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.