topic Incremental load using two fields in QlikView

Incremental load using two fields

apoorvasd — Tue, 23 Oct 2018 10:25:11 GMT

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.

Re: Incremental load using two fields

Gysbert_Wassenaar — Tue, 23 Oct 2018 12:49:48 GMT

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

Re: Incremental load using two fields

apoorvasd — Wed, 24 Oct 2018 06:18:22 GMT

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?

Re: Incremental load using two fields

apoorvasd — Thu, 25 Oct 2018 06:37:48 GMT

Hello Gysbert,

Did you get a chance to look into my question?

Re: Incremental load using two fields

Gysbert_Wassenaar — Thu, 25 Oct 2018 16:30:54 GMT

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

Re: Incremental load using two fields

apoorvasd — Fri, 26 Oct 2018 05:17:43 GMT

Hello Gysbert,

Thanks for your response.

Will check and see if this works!