Variable Evaluation in the Script

[colinodonnel]

Hello there,

I am a bit puzzled by this and wonder if anyone can explain please:

In the script I use variables to store and pass values e.g.

1 - A date is referenced in a table

2 - This date is passed to a variable using the following:

Let vReportMonthEnd =     date(peek('ReportMonthEnd', -1,'ReportMonthLatest'));

3 - This variable is then used later in the script, within another table for comparison purposes

Date('$(vReportMonthEnd)')               AS MaxDateClaimId,

The question is, why does the variable needed to be wrapped in single quote marks for it to evaluable properly???

I thought single quote marks were for strings, but this should be a number.

The following does not work:

 Date($(vReportMonthEnd))               AS MaxDateClaimId,

Many thanks,

Colin

[Anil_Babu_Samineni · Accepted solution]

Good question, Explanation here we go.

Example:  

'03/06/2019' // D/M/Y format

This string will interpret as string when we have data format as DD/MM/YYYY hence there is no impact to evaluate

Example:  

03/06/2019

When this string is used as a part of an expression, it will be numerically interpret as 3 divided by 6 divided by 2019.

[colinodonnel]

Yes, the date passed to the variable is in the format DD/MM/YYYY.

A great explanation. Still a little puzzled by one aspect.

The date format we see is effectively the text element of the underlying field value:  dual(text, number), and this is the textual representation of the underlying number e.g.  03/06/2019 is the text interpretation of the number 43616.

Does the dual format not apply to variables?

Also, why is there no need to use Date# in this case.

Thank you,

[JordyWegman]

Hi,

This is because you don't store it as a dual and Qlik doesn't recognize the 43616 as a date. 

When Qliks engine recognizes a date automatically, a Date# is not needed. A Date# is used to interpret the dates that Qliks engine is not recognizing and often used as a save option (Because versions of Qlik can differ in handling this). For example, 03062019 is a date, but Qlik does not recognize this and for this you need Date#.

Jordy

Climber

[colinodonnel]

Hi,

Thanks for the reply.

so hopefully this is right:

The following evaluates as an actual date:

Let vReportMonthEnd =     date(peek('ReportMonthEnd', -1,'ReportMonthLatest'));

In the variables box during debug mode, it is evaluated as:

vReportMonthEnd     30/04/2019  (Note: no quote marks around DD/MM/YYYY)

Furthermore in the Charts "=IsNum(vReportMonthEnd)" calculates as -1 (true).

However, during the script load, the formula:

Date('$(vReportMonthEnd)')  is evaluated as Date('30/04/2019').

Qlik Sense in this instance recognises that the string '30/04/2019' is a Date (due to matching the System Variables) so it gets processed as a Date.

Conversely, Date($(vReportMonthEnd)) is evaluated as Date(30/04/2019) which is really Date(0.03715) an evaluates to a date of 01/01/1800.

So perhaps a date of '30-04-2019' might not be recognised.

Cheers,

Colin