Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

kumarreddy257
Contributor

Incremental load

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

19 Replies
MVP
MVP

Re: Incremental load

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

kumarreddy257
Contributor

Re: Incremental load

Hi,

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

MVP
MVP

Re: Incremental load

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kumarreddy257
Contributor

Re: Incremental load

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

jaaldurgam
Valued Contributor

Re: Incremental load

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

MVP
MVP

Re: Incremental load

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kumarreddy257
Contributor

Re: Incremental load

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

jaaldurgam
Valued Contributor

Re: Incremental load

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

admin1
New Contributor III

Re: Incremental load

Check team_Key column whether it is unique

Community Browser