topic Re: Date format error / Sql in QlikView

Date format error / Sql

Wed, 25 Nov 2020 16:16:04 GMT

I am trying to load incrementing data, with the following statements.

From my initial QVD I extract the max date like so:

Tickets:
LOAD:
....

.....

....

.....

....

From test.qvd (qvd);

//Find last Create Date

Last_Create_Date:

LOAD max(date(CreateStamp)) as MaxDate

Resident Tickets;

//Store Max Create Date into Variable

LET Last_Create_Date = peek('MaxDate',0,'Last_Create_Date');

// Delete Tickets Table

DROP Table Tickets;

Then, in my Next load statement I am trying to load WHERE to_date(createdate) > $(Last_Create_Date)

However, its not working, it seems that the Sql is not understanding the format I am sending, because if I change it to WHERE to_date(createdate) > '2017-01-01' It works.

One thing that I noticed, is that if I load the variable into a text field as such =Last_Create_Date, I get the proper date, however, if I load it with $ expansion as =$(Last_Create_Date) I get 0.000192107580... Could it be that this is what Sql is also receiving?

Re: Date format error / Sql

Oleg_Troyansky — Thu, 20 Apr 2017 21:30:40 GMT

This is an interesting catch... when you use a $-sign expansion with a date, it evaluates the expression:

$(4/20/2017) = 4/20/2017 = 9.915716410510659e-5 (a very small number)

The solution is rather simple - enclose the $-sign expansion in single quotes:

WHERE to_date(createdate) > '$(Last_Create_Date)'

This is just one of many cool scripting techniques that Rob Wunderlich is teaching at the Masters Summit for Qlik. Check out if our agenda is right for you.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Re: Date format error / Sql

Fri, 21 Apr 2017 12:30:32 GMT

Thank you very much Oleg, it worked.