topic Re: update only current month records in incremental load in QlikView

update only current month records in incremental load

Anonymous — Tue, 24 Nov 2015 09:40:13 GMT

Hi All,

I am writing a script which is trying to update only current month data based on data available on daily.

I have one QVD file, insert and update files. If I run script it should update only current month records, if anything is update. If the records available in previous month it should not update.

Lets take i have QVD with the following data.

Qvd

Id Name date

1 Kp 201510

2 Pete 201510

3 Mike 201511

I have an excel file with the data

updates:

Id Name date

1 Kp1 201511

inserts:

Id Name date

4 juke 201511

my final QVd should be like below.

Id Name date

1 Kp 201510

2 Pete 201510

3 Mike 201511

4 juke 201511

1 kp1 201511

I have to update only current month records not previous month records. As I highlighted in red color. Kp is available in 201510 and 201511. It should be available in previous month and this month as well. If the same id update in December it should available in 3 months as well.

I have written below script, but it is not working

updates:

LOAD id,

name,

amount,

date

FROM

account_updates.xlsx

(ooxml, embedded labels, table is Sheet1)

where Month(date)<>Month(today());

If FileSize('12.qvd') > 0 then

LOAD *

FROM D:\insertsupdates\12.QVD(qvd)

WHERE NOT Exists(id);

ENDIF;

Concatenate(updates)

LOAD id,

name

FROM

account_inserts.xlsx

(ooxml, embedded labels, table is Sheet1)

where not Exists(id);

STORE updates into D:\insertsupdates\12.QVD(qvd);

Pls help me on this one.

Regards,

Kumar

Re: update only current month records in incremental load

Chanty4u — Tue, 24 Nov 2015 10:07:46 GMT

hi,

Please chk below link

https://community.qlik.com/docs/DOC-9531

hope this helps u

Thanks

Suresh

Re: update only current month records in incremental load

Peter_Cammaert — Tue, 24 Nov 2015 10:21:32 GMT

Should be pretty straightforward. You explanation says that all records are identified by way of a YYYYMM and a Name value. That should be your primary key. I guess the following series of steps should do what you want:

Load all updates for the current month in a new resident table. Create a PK field.
Load all inserts for the current month into the same table on condition that their PK doesn't exist yet. Add a PK value to those records.
Load all history rows from the QVD into the same resident table except those whose PK already exists in the resident table.

In the end, and before storing your resulting table, you can drop the PK field.

Best,

Peter

Re: update only current month records in incremental load

Anonymous — Tue, 24 Nov 2015 11:04:46 GMT

Hi Peter,

Thanks for your comments.

I have a small doubt here and already having PK as Id in my table. Please tell is it require to create another PK.

If you don't mind can you modify my script which I have pasted in my earlier post.

Regards,

Kumar

Re: update only current month records in incremental load

Peter_Cammaert — Tue, 24 Nov 2015 11:15:25 GMT

Your original PK is not a PK because it doesn't uniquely identify rows. Moreover, a composite key (Name and YYYYMM) will simplify considerably the check for existing rows.

The new PK has to be created only during the two small LOADs (1 and 2). during step 3, you only check for existence of every historic value as you do not want the history to overwrite the new updates. After that you can throw away the temporary PK. So no need to store it in the QVD that you may want to STORE.