11 Replies Latest reply: Apr 21, 2017 12:29 PM by Mark Ritter RSS

    Trouble Setting a Variable

    Mark Ritter

      In my script I am trying to handle what happens when we switch to a new year.

       

      So I want to set a variable with the value of the date-time for the start of the year.

       

      For example when we move to 2018  I want the variable to contain the numeric value for 01/01/2018 00:00:00

       

      I have not quite figured out the correct combination to make this happen.

       

      Thanks for your help.

        • Re: Trouble Setting a Variable
          Sunny Talwar

          May be this

           

          TimeStamp(YearStart(Today()), 'DD/MM/YYYY hh:mm:ss')

          • Re: Trouble Setting a Variable
            Bill Markham

            Set it to the value of :

             

                timestamp(yearstart(today()))

              • Re: Trouble Setting a Variable
                Mark Ritter

                Here is my issue. 

                The data in my file is formatted with the Qlik numeric value for date and time. 

                So the field contains a value like 41264.748611111

                 

                In my script I need a variable that I can compare this to and select the records that were added after this date/time.

                 

                So I want my variable to have the value 43101.00000000 (start of 2018). 

                 

                I have tried all of the recommendations above and none of them are working.  I can create a variable using them.  But when I run the script with the where clause below it does not work.

                 

                Here is my load:

                LOAD * ;

                  SQL SELECT *

                  from ArmorData.dbo.StopData

                  WHERE ImportTime > $(vImportTime);

                  • Re: Trouble Setting a Variable
                    Sunny Talwar

                    Try this

                     

                    LET vImportTime = Date(YearStart(Today()), 'DD-MMM-YYYY');

                     

                    LOAD * ;

                    SQL SELECT *

                    FROM ArmorData.dbo.StopData

                    WHERE ImportTime > TO_DATE('$(vImportTime)', 'DD-MON-YYYY');

                    • Re: Trouble Setting a Variable
                      Bill Markham

                      I had a similar issue a while back whilst extracting data from an Oracle database.

                       

                      The problem was solved by ensuring similar date formats, which I did using the Oracle to_date function to create an Oracle data format value from the Qlik variable value like this :

                       

                      ... where f.last_updated > to_date(~' & date($(vMaxDate), 'YYYY-MM-DD hh:mm') & '~, ~YYYY-MM-DD HH24:MI~)

                        • Re: Trouble Setting a Variable
                          Mark Ritter

                          Let me take a step back.

                           

                          The current process runs just fine.  But it is anticipating the data is in the current year of 2017.  I am trying to remove any hard coded logic and replace with variables so that the transition to next year will be automatic.

                           

                          Step 1. 

                          Read the current QVD and find the last date time which is stored in a variable.

                          DeltaSetup:

                            LOAD

                            MAX(ImportTime) AS DeltaMaxValue

                                      FROM [lib://QVDS/DTRAK\Load\StopData$(vCurrentYear).QVD] (QVD);   

                           

                          // CAPTURE THE FIELD VALUE

                          LET vDeltaFieldValue = TIMESTAMP(PEEK('DeltaMaxValue' , 0 , 'DeltaSetup'));

                           

                          DROP TABLE DeltaSetup;

                           

                          Step 2.

                          Load any new data after the above variable date.

                          StopData:

                            LOAD * ;

                            SQL SELECT *

                            from ArmorData.dbo.StopData

                            WHERE ImportTime > '$(vDeltaFieldValue)';

                           

                          Step 3

                          Concatenate the new data with the QVD.

                          CONCATENATE (StopData)

                            LOAD *

                                      FROM [lib://QVDS/DTRAK\Load\StopData$(vCurrentYear).QVD] (QVD);

                           

                          At the start of the next year I have a problem.  There is no QVD for 2018 created yet so this entire job would bomb.  I have an if added to check for the existence of the file.  If no file then I want to run just Step 2

                           

                          So what I want is to run just step 2 and create a new QVD for 2018 but only load any data that is greater than the start of the year.  All other data would have already been loaded into the 2017 QVD.

                           

                          Hopefully, this makes sense and there is a way to do this.