Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

Incremental load using two fields

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.

5 Replies
Gysbert_Wassenaar

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);


talk is cheap, supply exceeds demand
apoorvasd
Creator II
Creator II
Author

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?

apoorvasd
Creator II
Creator II
Author

Hello Gysbert,

Did you get a chance to look into my question?

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
apoorvasd
Creator II
Creator II
Author

Hello Gysbert,

Thanks for your response.

Will check and see if this works!