topic Re: Date restricted load in QlikView

Date restricted load

Wed, 26 Aug 2015 10:03:47 GMT

I am trying to reduce the data loaded from a table using the date field within it.

To avoid links to other tables using the same field name I have qualified the table but I think this is causing me the issue with my where statement.

Table is named SalesOrderLinesHistory (original table is orddeth)

Field is o-date (so appears as SalesOrderLinesHistory.o-date in the Qlik table view)

Qlik table shows SalesOrderLinesHistory.o-date column is formatted correctly as dd/mm/yyyy

ATTEMPT 1

I added WHERE "o-date" >= Date(27/04/2013);

after the FROM PUB.orddeth statement but I get the error:

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date(27/04/2013)" (10713)

ATTEMPT 2

I added WHERE "o-date" >= 27/04/2013;

after the FROM PUB.orddeth statement but I get the error:

SQL##f - SqlState: S1000, ErrorCode: 4294947288, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481)

Re: Date restricted load

tresB — Wed, 26 Aug 2015 10:10:09 GMT

Have you tried with single quotes like:

WHERE o-date >= '27/04/2013' ; ?

Re: Date restricted load

kaushiknsolanki — Wed, 26 Aug 2015 10:11:01 GMT

Hi,

This error is related to the SQL syntax.

You must look at the SQL functions which will convert the text into date and then you can use that in SQL select statement.

Regards,

Kaushik Solanki

Re: Date restricted load

Wed, 26 Aug 2015 11:15:36 GMT

Can you elaborate please Kaushik ?

Thanks

Re: Date restricted load

Wed, 26 Aug 2015 11:18:12 GMT

Thanks for tresesco but I still get an error.

I believe the o-date field maybe loaded as text so the criteria may not be working.

Re: Date restricted load

sasiparupudi1 — Wed, 26 Aug 2015 11:21:35 GMT

use a variable

set x="Date(Date#('27/04/2013','DD/MM/YYYY'),'DD/MM/YYYY')";

WHERE o-date>='$(x)';

hth

Sasi