update only current month records in incremental load

[Anonymous]

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

[Chanty4u]

hi,

Please chk below link

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

hope this helps u

Thanks

Suresh

[Peter_Cammaert]

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

[Anonymous]

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

[Peter_Cammaert]

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.