Incremental load

[Anonymous]

Hello,

I am new to qlikview. In my project we are doing incremental load and getting data as qvx files.

On daily basis qvx files are coming and they are coming only incremental data only, but in my script it is converting into qvd from that incremental script only. It is not appending to existing QVD with incremental data.

Inserts:

LOAD *

FROM

$(Vqvxpath)packets_inserts.qvx(qvx);

date_packets:

LOAD Max(packet_record_updated_datetime) as packets_Maxdate

Resident Inserts;

LET VDate_packets = Peek('packets_Maxdate',0,'date_packets');

DROP Table date_packets;

STORE Inserts into $(Vqvdpath)packets.QVD(qvd);

DROP Table Inserts;

Updates:

LOAD *

FROM

$(Vqvxpath)packets_updates.qvx(qvx)

Where (packet_record_updated_datetime >'$(VDate_packets)') ;

Concatenate

LOAD *

FROM

$(Vqvdpath)packets.qvd(qvd)

Where not Exists(team_key);

STORE Updates into $(Vqvdpath)packets.QVD(qvd);

Pls help on this issue.

Regards,

Kumar

[maxgro]

add this statement

trace $(VDate_packets) ;

and see if you get a num o r  a  date

and also if the format is the same of packet_record_updated_datetime

[Anonymous]

Hi,

Can you help me where can i add trace $(VDate_packets) in the script

[jonathandienst]

Change this line:

LET VDate_packets = Peek('packets_Maxdate',0,'date_packets');

to

LET VDate_packets = Num(Peek('packets_Maxdate',0,'date_packets'));

And this line

Where (packet_record_updated_datetime >'$(VDate_packets)') ;

to

Where (packet_record_updated_datetime > $(VDate_packets)) ;

This removes possible confusion due to date formatting.

[Anonymous]

Hi,

My requirement is here, I have written incremental script as above. In full load it is loading and converting  QVD.

In incremental load I will get only limited records as QVX file, if i run script, QVD will be overriding with existing QVD instead of appending new records to existing QVD. So I want to check whether I have written the code is correct or not. I mean it will override or it will do append.

Regards,

Kumar

[SreeniJD]

Reddy -

Please be noted that there won't be any appending to QVD... QV always creates new QVD with the timestamp... that means every time it will create a new QVD.

HTH

SReeni

[jonathandienst]

Sreeni is correct. The QVD is overwritten on each STORE. Make these changes:

Change this:

STORE Inserts into $(Vqvdpath)packets.QVD(qvd);

DROP Table Inserts;

Updates:

LOAD *

FROM

$(Vqvxpath)packets_updates.qvx(qvx)

Where (packet_record_updated_datetime >'$(VDate_packets)') ;

Concatenate

LOAD *

FROM

$(Vqvdpath)packets.qvd(qvd)

Where not Exists(team_key);

To this:

Concatenate(Inserts)

LOAD *

FROM

$(Vqvxpath)packets_updates.qvx(qvx)

Where (packet_record_updated_datetime >'$(VDate_packets)') ;

Concatenate(Inserts)

LOAD *

FROM

$(Vqvdpath)packets.qvd(qvd)

Where not Exists(team_key);

[Anonymous]

Hi Sreeni,

Thanks for giving reply to my query.

My requirement is Everyday I will get inserts and updates QVX files. If run I run this QVD generation script today it will create  one QVD file. Tomorrow I will get some more inserts and updates QVX files which will contain new records. If I run now, that existing records are overwriting with newly records. But it should not happen and I want to see yesterday's data and today's data also.

This QVX files are getting overwriting everyday, but my my QVD should not overwrite and should add new records to the existing records.

Is it possible to new add records to existing records based on my daily QVX files.

Please help on this one.

Regards,

Kumar

[SreeniJD]

Hi Kumar,

To preserve the history, implement Slowly Changing Dimensions (SCD-2) where you can keep the historic information with an additional column like timestamp or flag...

Let me know if you need any help.

HTH

Sreeni

[Anonymous]

Check team_Key column whether it is unique