topic Where condition in SQL statement in QlikView

Where condition in SQL statement

Tue, 14 Jun 2011 13:13:35 GMT

I want to filter the data I load into Qlikview by date and this command runs on Oracle fine but Qlikview gets stuck when I execute this query.

SalesHeader:

LOAD "CUSTOMER_ACCOUNT" as CustCode,

"FIRST_ORDER",

"ORDER_COST" as Cost,

"ORDER_DATE" as Date,

"ORDER_VALUE" as Value,

"SALES_DOCUMENT_NUM" as DocumentNo,

"ORDER_VALUE"-"ORDER_COST" as Margin,

"ORDER_DATE" as SaleDate;

SQL SELECT "CUSTOMER_ACCOUNT",

"FIRST_ORDER",

"ORDER_COST",

"ORDER_DATE",

"ORDER_VALUE",

"SALES_DOCUMENT_NUM"

FROM TEST."SALES_HEADER"

where order_date >=1072915200;

Please help.

thanks in advance.

Lavanya

Where condition in SQL statement

pat_agen — Tue, 14 Jun 2011 13:19:05 GMT

hi,

try and cast the order_date and your test using the to_date() function from Oracle. It is probably the the odbc layer that is throwing up the error.

Re: Where condition in SQL statement

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 13:20:48 GMT

Hello Lavanya,

Although QlikView date format is a numeric value as well, you may need to do some transformation in your dates when pulling info from Oracle, something like

SalesHeader:
LOAD "CUSTOMER_ACCOUNT" as CustCode,
    "FIRST_ORDER",
    "ORDER_COST" as Cost,
    "ORDER_DATE" as Date,
    "ORDER_VALUE" as Value,
    "SALES_DOCUMENT_NUM" as DocumentNo,
    "ORDER_VALUE"-"ORDER_COST" as Margin,
    "ORDER_DATE" as SaleDate;
SQL SELECT "CUSTOMER_ACCOUNT",
    "FIRST_ORDER",
    "ORDER_COST",
    "ORDER_DATE",
    "ORDER_VALUE",
    "SALES_DOCUMENT_NUM"
FROM TEST."SALES_HEADER"
where TO_DATE(ORDER_DATE, 'DD/MM/YYYY') >= TO_DATE('01/01/2010', 'DD/MM/YYYY');

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Where condition in SQL statement

Tue, 14 Jun 2011 13:32:11 GMT

Thanks for your reply Miguel.

Unfortunately, the values in Order_date is a 10 digit number which is calculated as

trunc(to_date('01-Jan-1970')+("ORDER_DATE"/86400))

Here ORDER_DATE is some date like 31Jul2009

In Oracle data base typical order_date value is stored as 1248999003 which is equal to 31-Jul-2009.

I wanted to filter the data greater than 1072915200.

I shall try to_date option if it works anyway.

Where condition in SQL statement

Tue, 14 Jun 2011 13:35:25 GMT

I made the changes as below but still the same problem. Qlikview gets into NotResponding mode.

SalesHeader:

LOAD "CUSTOMER_ACCOUNT" as CustCode,

"FIRST_ORDER",

"ORDER_COST" as Cost,

"ORDER_DATE" as Date,

"ORDER_VALUE" as Value,

"SALES_DOCUMENT_NUM" as DocumentNo,

"ORDER_VALUE"-"ORDER_COST" as Margin,

"ORDER_DATE" as SaleDate;

SQL SELECT "CUSTOMER_ACCOUNT",

"FIRST_ORDER",

"ORDER_COST",

"ORDER_DATE",

"ORDER_VALUE",

"SALES_DOCUMENT_NUM"

FROM TEST."SALES_HEADER" where

trunc(to_date('01-Jan-1970')+("ORDER_DATE"/86400)) >=to_date('01-Jan-2004');