topic Re: Delta load using flag in QlikView

Delta load using flag

Tue, 27 May 2014 04:33:45 GMT

Hi,

I want to do delta load for a table, where there is no date field instead there is a flag field.

This table is not insert only, existing records will also get updated (if there is a change in the record, then we will expire the old record with currentflag status ‘N’ and insert new record with currentflag ‘Y’).

Can someone help me with the script to achieve delta load for this table.

Many Thanks,

Swathi

Re: Delta load using flag

Tue, 27 May 2014 05:02:20 GMT

Hi, swathi.

Please provide sample data to better understand.

Re: Delta load using flag

Tue, 27 May 2014 05:13:05 GMT

Hi Alex,

I'm sorry i do not have any sample data for this requirement, intead i collected few information as below;

1. This table holds Employee details.

2. A dimension table which holds millions of records

3. Insert and Update table eg., New employee added will be insert, and salary, promotion details will be updated. No delete of records from employee table.

4. Doesnt have any of the date field to proceed with the delta logic.

5. Instead there is a "CurrentFlag", on top of this field delta logic is to be applied.

6. Current Flag - Holds only two data in this column which Y and N.

5. if there is a change in the record, then will expire the old record with currentflag status ‘N’ and insert new record with currentflag ‘Y’.

Many Thanks,

Swathi

Re: Delta load using flag

Tue, 27 May 2014 05:17:39 GMT

Do you have key field in this table?

Re: Delta load using flag

Tue, 27 May 2014 05:24:12 GMT

Do you can read QlikView help file?

Pleas try search "Using QVD Files for Incremental Load".

Case 3 is your case and i think it is better implementation.

Re: Delta load using flag

Tue, 27 May 2014 05:30:01 GMT

yes i have a key field in this table.

But no date field.

Re: Delta load using flag

Tue, 27 May 2014 05:37:55 GMT

Try use this:

QV_Table:

LOAD

PrimaryKey,

SQL

SELECT

PrimaryKey,

FROM DB_TABLE

WHERE flag= 'Y'; //Load only modified data from db

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey); //Add all unmodified data from QDV

STORE QV_Table INTO File.QVD; //store back updated data into qvd

Re: Delta load using flag

Tue, 27 May 2014 05:48:21 GMT

Thanks Alex,

I shall try this approach.

Re: Delta load using flag

Tue, 27 May 2014 05:58:41 GMT

You are welcome.