Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

2 Replies
flipside
Valued Contributor II

Re: Registering changes in a dataset and flagging the rows

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

Not applicable

Re: Registering changes in a dataset and flagging the rows

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

Community Browser