DateStamp - TimeStamp - Historical data - Each Reload

[#1]

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.

[swuehl]

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.

[#3]

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.

[swuehl]

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

[#5]

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

[swuehl]

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.