topic Re: time stamp driven incremental load in QlikView

time stamp driven incremental load

Anonymous — Fri, 13 May 2016 02:03:43 GMT

Still new to Qlikview.

I am trying to do some incremental download using QVD and adding more files and store it to a n updated QVD file.

The Data contains time stamp. So far I have downloaded some QVD files. Now, I'd like to add some csv files with criteria where the latest time stamp is greater than the latest time stamp in QVD file. What I did was

Incremental_CDR:

LOAD Distinct

[Time Stamp], B,C,D,E,F,

FROM [..\QVD\Filename.qvd] (qvd);

//Last_Record:

LOAD Max (timestamp([Time Stamp])) as Latest_Time_Stamp

Resident Incremental_CDR;

LET $(vLast_Record)= Peek('[Latest_timestamp]', 0, 'Last_Record)');

trace >>> $(vLast_Record);

//Load new Records

concatenate

LOAD Distinct

FROM

$(vSourceData)Filename_*.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where Timestamp([Time Stamp]) > $(vLast_Record);

but it failing with error as follows:

Field not found - <>>

concatenate

LOAD Distinct ...... and rest of the message

When I changed the $(vLast_Record) to a date(e.g. '09/05/2016') it works, but I want to to make it more precise and need to rely on the latest time stamp in last record so that I can append the qvd data and next time do another incremental load will simply use the latest time stamp in the latest qvd file or last record in another source.

Any clue or help?

Re: time stamp driven incremental load

Gysbert_Wassenaar — Fri, 13 May 2016 14:32:40 GMT

LOAD Distinct

FROM

$(vSourceData)Filename_*.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where Timestamp([Time Stamp]) > $(vLast_Record);

You haven't specified any fields to retrieve from the csv file. Perhaps LOAD Distinct * ....etc... does what you want.

Re: time stamp driven incremental load

Anonymous — Sat, 14 May 2016 00:29:02 GMT

Hi,

Actually I did, just forgot to put in the example above. It should be like this

LOAD Distinct

[Time Stamp], B,C,D,E,F,

FROM

$(vSourceData)Filename_*.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where Timestamp([Time Stamp]) > $(vLast_Record);

It keeps showing the same error

Re: time stamp driven incremental load

Gysbert_Wassenaar — Sat, 14 May 2016 12:13:37 GMT

Remove the comma after the F.

Re: time stamp driven incremental load

Anonymous — Sat, 14 May 2016 13:11:06 GMT

Hi, Yes I did. Actually field names are just the examples. No comma after

real filed names.

Also, no value after trace command - trace >>> $(vLast_Update_Record);

On Sat, May 14, 2016 at 7:14 AM, Gysbert Wassenaar <qcwebmaster@qlikview.com

Re: time stamp driven incremental load

Gysbert_Wassenaar — Sat, 14 May 2016 14:11:48 GMT

//Last_Record:

LOAD Max (timestamp([Time Stamp])) as Latest_Time_Stamp

Resident Incremental_CDR;
LET $(vLast_Record)= Peek('[Latest_timestamp]', 0, 'Last_Record)');

Remove the square brackets: Peek('Latest_timestamp', 0, 'Last_Record)');

Remove the ) character after Last_Record: Peek('Latest_timestamp', 0, 'Last_Record');

Use the correct field name: Peek('Latest_Time_Stamp', 0, 'Last_Record');

You might also want to make sure you get a numeric value back: num(Peek('Latest_Time_Stamp', 0, 'Last_Record'));

Re: time stamp driven incremental load

Anonymous — Sat, 14 May 2016 15:57:13 GMT

https://www.google.co.in/search?site=&source=hp&q=incremental+load+in+qlikview&oq=incremental+load&gs_l=hp.1.0.0l10.928.…

Re: time stamp driven incremental load

Anonymous — Sun, 15 May 2016 18:46:22 GMT

I fixed all syntax but still got the same message.

trace >>> $(vLast_Record); also did not return any value. Is there anything missing?

If I changed the condition to certain date it works but I want to make it more autonomous so that I will simply append the data everything I run the script instead of manually change the date or even time.

Any suggestion or help from the experts in this community?

Thanks in advance

Re: time stamp driven incremental load

Gysbert_Wassenaar — Mon, 16 May 2016 06:34:45 GMT

Post an example qlikview document that demonstrates the problem along with two source files that contain at least the timestamp field.

Re: time stamp driven incremental load

Anonymous — Wed, 18 May 2016 21:53:17 GMT

Hi, Please find attached the sample

Re: time stamp driven incremental load

rwunderlich — Thu, 19 May 2016 00:24:32 GMT

This statement is incorrect:

LET $(vLast_Record)= Peek('[Latest_timestamp]', 0, 'Last_Record)');

It should be:

LET vLast_Record= Peek('[Latest_timestamp]', 0, 'Last_Record)');

no $() on the variable name.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: time stamp driven incremental load

Anonymous — Thu, 19 May 2016 03:12:29 GMT

Rob, Many thanks for spotting this!

It is very helpful and now I'm getting closer to the final step, but not there yet. The error is gone after fixing the statement and trace shows value of the last record in initial run, but when running with the new record no new records are added or concatenated. It only load the qvd file that has only the initial record although the condition to include the new records is met.

// Load new records

Concatenate

Load distinct

Field names

From source (*.csv)

Where > 'vLast_Record';

Am I missing something?

Additionally, I'd also like to update the latest time stamp with the latest record in the latest loaded file.

Would appreciate any help on this.

Re: time stamp driven incremental load

rwunderlich — Thu, 19 May 2016 16:47:55 GMT

Can you post a document log?

-Rob

Re: time stamp driven incremental load

Anonymous — Thu, 19 May 2016 19:35:39 GMT

The log is attached

Re: time stamp driven incremental load

rwunderlich — Thu, 19 May 2016 22:09:26 GMT

You do need the $() when you reference the variable this way. Just don't use the $() when you set the variable with SET or LET.

where [Time Stamp] >= '$(vLast_Record)'

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: time stamp driven incremental load

Anonymous — Thu, 19 May 2016 23:32:22 GMT

Thanks so much for the tips and explaining the difference! It works.

A simple mistake that makes a big difference.

On Thu, May 19, 2016 at 5:10 PM, Rob Wunderlich <qcwebmaster@qlikview.com>