Registering changes in a dataset and flagging the rows

[#1]

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

OrderID	Amount	Frequency	Price
1001	3	2	10
1002	8	3	8
1003	5	2	9

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

OrderID	Amount	Frequency	Price
1001	3	2	10
1002	6	3	8
1003	5	2	9

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

[flipside]

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

[#3]

Hi flipside, thank you very much for a concise and helpfull answer.

I have gotten your solution to work and I'm now working on inspecting the inner workings of it and trying to get a solid understanding of what's going on.

Thank you very much for now and I'll keep this thread updated as I go along.

--

Christian