Date load error

[didierodayo]

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

[Anil_Babu_Samineni]

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

[didierodayo]

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

[tamilarasu · Accepted solution]

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;

[Anil_Babu_Samineni]

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;

[didierodayo]

You are right Tamil,

that is what it was. Thank you

[tamilarasu]

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

[Anil_Babu_Samineni]

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

[tamilarasu]

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

[didierodayo]

Hi Anil,

Just confirming the brackets work as well.

Thanks