topic Re: DateStamp - TimeStamp - Historical data - Each Reload in QlikView

DateStamp - TimeStamp - Historical data - Each Reload

Tue, 02 Jul 2013 08:22:46 GMT

Good morning everybody,

I need your help please.

I have a look on a SQL view whose i must historicize each row. That's my problem.

I haven't a column with a dateStamp.

So i want to:

1) Know how i can insert a column (or an other solution) with "DateStamp" for each reload.

2) Have the last reload date on my dashboard.

Thank you for you answer.

Re: DateStamp - TimeStamp - Historical data - Each Reload

swuehl — Tue, 02 Jul 2013 08:58:50 GMT

Not sure what you exactely want to achieve, but to answer your questions:

1) You can create another field with your datestamp like

LOAD *,

Today(1) as DateStamp;

SQL SELECT

.....

FROM .... ;

2) You can retrieve the last successful reload time stamp using ReloadTime() system function

Create a text box, use

=ReloadTime()

You may also want to look into 'incremental loads', e.g. in the QlikView Cookbook you can download here

or searching this forum.

Re: DateStamp - TimeStamp - Historical data - Each Reload

Tue, 02 Jul 2013 09:48:10 GMT

Thank you for your fast answer.

For the 2) point that's exactly what i looked for.

but for the 1) point, i need to conserve all the dateStamp.

For example:

Yesterday loaded :

StudentNumber StudentName StudentAdress StudentPhone DateStamp

S - 10000 Barbara Mamer New York USA 00 1 857 01010 01/07/2013

Today I load :

StudentNumber StudentName StudentAdress StudentPhone DateStamp

S - 10000 Barbara Mamer New York USA 00 1 857 01010 02/07/2013

Tomorrow i will load :

StudentNumber StudentName StudentAdress StudentPhone DateStamp

S - 10000 Barbara Mamer Boston USA 00 1 857 01010 03/07/2013

With the DateStamp i will have all details about each row with modification or not. (here for example, the student changed her city adress)

How can i do this please?

Thank you for your answer.

Re: DateStamp - TimeStamp - Historical data - Each Reload

swuehl — Tue, 02 Jul 2013 10:10:39 GMT

You'll need to use qvd files to store your historic data.

Please check the above referenced cookbook (it's collection of useful QV applications, all PE enabled and well documented). Please check also the reference manual for qvd files and incremental loads:

28 QVD Files

28.4 Using QVD Files for Incremental Load

Re: DateStamp - TimeStamp - Historical data - Each Reload

Tue, 02 Jul 2013 13:03:52 GMT

Thank you for your answer but my problem is how to configure and where store the"modificationDate" ?

Typically the script is:

Let ThisExecTime = Now();

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

Thank you

Re: DateStamp - TimeStamp - Historical data - Each Reload

swuehl — Tue, 02 Jul 2013 13:11:39 GMT

In this script, ModificationTime is a field from your data source. You said, you haven't got a column with a date / time stamp, so this won't work for you.

But you can read all data from the source on each load, so you "will have all details about each row with modification or not.", add your own timestamp and historize.