Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rittermd
Honored Contributor

Trouble Setting a Variable

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.

11 Replies

Re: Trouble Setting a Variable

May be this

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

Re: Trouble Setting a Variable

Set it to the value of :

    timestamp(yearstart(today()))

rittermd
Honored Contributor

Re: Trouble Setting a Variable

I guess I didn't ask the question correctly.

I don't want the variable to have the value '01/01/2018 00:00:00'

I want it to have the value 43101.00000000000 if that is possible

Re: Trouble Setting a Variable

May be this

Num(YearStart(Today())

or

Num(YearStart(Today(), #.00000000000)

Re: Trouble Setting a Variable

Timestamps are held as dual values, as in they hold both the numeric and human readable values.

This is an old Blog Post by HIC, but the concepts are still the same as they were 5 years ago.

Get the Dates Right

rittermd
Honored Contributor

Re: Trouble Setting a Variable

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

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');

rittermd
Honored Contributor

Re: Trouble Setting a Variable

Get an error on TO_Date and get an error on format of the LET statement.  Doesn't like the comma.

Re: Trouble Setting a Variable

Can you share a screenshot for the variables? What date format do you supply when you run the query outside of QlikView in Toad?

Community Browser