Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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
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.