Trouble Setting a Variable

[rittermd]

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.

[Anonymous]

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~)

[rittermd]

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.