Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I need help in doing incremental load using two fields. The scenario that I have here is as follows,
There are two fields in the database say, "ID" and "STATUS". All these days I was doing the incremental load using the ID field, i.e., to load only the IDs which are greater than the ones loaded in the previous load already.
But the issue here is, it is possible that we have multiple rows with same ID, but different STATUS. So in this case, if a particular ID is already loaded into QlikView and a new row for the same ID is added with a different STATUS, then this will not be updated in QlikView as the ID would have already been loaded into QlikView. Hence, I am trying to do incremental load based on both the fields.
So, for example if we have the following records loaded into QlikView,
ID STATUS
1 23
2 34
2 48
Incremental load logic should be like, for a particular ID, load the record with highest STATUS value for that ID. So in my example above, for ID 2, the second record has to fetched since the STATUS value here is highest among all for ID 2.
Any help on this?
Thank you.
Use an SQL statement that retrieves only the max status for each id:
Data:
LOAD DISTINCT ID, STATUS FROM D:\QVD\HistoricalQVD.qvd (qvd);
CONCATENATE (Data)
SELECT ID, MAX(STATUS) AS STATUS FROM mydatabase.mytable GROUP BY ID;
STORE Data INTO D:\QVD\HistoricalQVD.qvd (qvd);
Hello,
Thank you very much for your response.
Have a small question. There are other fields in the table to be loaded. So, in the group by clause should I mention all these field names?
Hello Gysbert,
Did you get a chance to look into my question?
You'll need a different sql statement. Something like
SELECT a.ID, a.STATUS, a.FieldA, a.FieldB, ...etc...
FROM mydatabase.mytable a
INNER JOIN (
SELECT ID, MAX(STATUS) STATUS
FROM mydatabase.mytable
GROUP BY ID
) b ON a.ID = b.ID AND a.STATUS = b.STATUS
Hello Gysbert,
Thanks for your response.
Will check and see if this works!