5 Replies Latest reply: Jul 2, 2013 9:11 AM by Stefan Wühl RSS

    DateStamp - TimeStamp - Historical data - Each Reload

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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

                      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