topic Re: Date load error in QlikView

Date load error

didierodayo — Tue, 12 Sep 2017 05:06:44 GMT

Hello,

I am not sure why this error is occurring and need some help.

Error Code:

Unknown statement

OIP_Invoices_2015-11-12:

SQL SELECT *

FROM Mytable

WHERE Date >= '2015-11-12'

Here is my script

LET vL.Start = date#(Today()-670,'YYYYMMDD');

FOR vL.spin = $(vL.Start) to Today()-1

LET vProcessDate = Text(Date($(vL.spin), 'YYYY-MM-DD'));

Mytable_$(vProcessDate):

SQL SELECT *

FROM Mytable

WHERE Date = '$(vProcessDate)';

STORE Mytable_$(vProcessDate) INTO '$(vQVDExtractPath)Mytable_$(vProcessDate).qvd';

DROP TABLE Mytable_$(vProcessDate);

ENDIF;

NEXT;

The date field is store in this format YYYY-MM-DD hh:mm:ss.000. But when I run the script below it works fine.

SQL SELECT *

FROM Mytable

WHERE Date >= '2015-11-12'

Thanks

Re: Date load error

Anil_Babu_Samineni — Tue, 12 Sep 2017 05:17:31 GMT

For me this is working? Can you check whether the Date field is Date only or something different?

LET vL.Start = date#(Today()-670,'YYYYMMDD');

FOR vL.spin = $(vL.Start) to Today()-1

LET vProcessDate = Date($(vL.spin), 'YYYY-MM-DD');

LOAD * Inline [

DateField

2015-11-12

2015-11-05

2014-11-04

2013-03-20

] Where DateField = '$(vProcessDate)';

And even with >= also working fine

LET vL.Start = date#(Today()-670,'YYYYMMDD');

FOR vL.spin = $(vL.Start) to Today()-1

LET vProcessDate = Date($(vL.spin), 'YYYY-MM-DD');

LOAD * Inline [

DateField

2015-12-23

2015-11-13

2015-11-12

2015-11-05

2014-11-04

2013-03-20

] Where DateField >= '$(vProcessDate)';

Re: Date load error

didierodayo — Tue, 12 Sep 2017 05:32:24 GMT

Yes Anil it is a date field store as YYY-MM-DD hh:mm:ss.000

Re: Date load error

tamilarasu — Tue, 12 Sep 2017 05:37:48 GMT

Hi Didier,

Looks like the hyphen is not acceptable while defining the table name in Qlikview.

Try this script.

LET vL.Start = date#(Today()-670,'YYYYMMDD');

FOR vL.spin = $(vL.Start) to Today()-1

LET vProcessDate = Date($(vL.spin), 'YYYY-MM-DD');

[Mytable_$(vProcessDate)]:

SQL SELECT *

FROM Mytable

WHERE Date = '$(vProcessDate)';

STORE [Mytable_$(vProcessDate)] INTO '$(vQVDExtractPath)Mytable_$(vProcessDate).qvd';

DROP TABLE [Mytable_$(vProcessDate)];

NEXT;

Re: Date load error

Anil_Babu_Samineni — Tue, 12 Sep 2017 05:40:01 GMT

Can you work something like below?

LET vL.Start = date#(Today()-670,'YYYYMMDD');

FOR vL.spin = $(vL.Start) to Today()-1

LET vProcessDate = Text(Date($(vL.spin), 'YYYY-MM-DD'));

Mytable_$(vProcessDate):

SQL SELECT *

FROM Mytable

WHERE Date(Date#(Date,'YYY-MM-DD hh:mm:ss.fff'),'YYYY-MM-DD') = '$(vProcessDate)';

STORE Mytable_$(vProcessDate) INTO '$(vQVDExtractPath)Mytable_$(vProcessDate).qvd';

DROP TABLE Mytable_$(vProcessDate);

ENDIF;

NEXT;

Re: Date load error

didierodayo — Tue, 12 Sep 2017 05:46:34 GMT

You are right Tamil,

that is what it was. Thank you

Re: Date load error

tamilarasu — Tue, 12 Sep 2017 05:53:01 GMT

No problem Didier. If your query is resolved, please take a moment to close the thread. Have a good day!

Re: Date load error

Anil_Babu_Samineni — Tue, 12 Sep 2017 06:06:26 GMT

Just to know, can this done by using Square Bracket like [Mytable_$(vProcessDate)] until unless we can go variable?

Re: Date load error

tamilarasu — Tue, 12 Sep 2017 06:17:41 GMT

I agree your point Anil. I have updated the script with the changes. Thank you and have a good day!

Re: Date load error

didierodayo — Tue, 12 Sep 2017 06:23:11 GMT

Hi Anil,

Just confirming the brackets work as well.

Thanks