topic Script Error - Field not found in QlikView

Script Error - Field not found

Fri, 10 Sep 2010 18:56:21 GMT

Hi...

I am trying to load a table into qlikview but getting field not found error. The fields exist in the database.

Can someone help me with what I am missing out here?

Orders_Tab:
LOAD

date(floor(order_date)) AS link_Date,
date(floor(order_date)) AS Order_Date, //Load it a second time for making date range selections

order_Date - yearstart(order_Date) + 1 AS OrderDayWithinYear,
order_Date - monthstart(order_Date) + 1 AS OrderDayWithinMonth,

OID as OrderId,
scp_oid as SCP_Id,
entity_oid as Entity_Id;

SQL
SELECT oid,order_date,scp_oid,entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1;

-----------------------Script Error-------------------------------------------------------

Field not found - <order_date>
SQL
SELECT oid,order_date,scp_oid,entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1

Thank You.

Regards

Kumar

Script Error - Field not found

Miguel_Angel_Baeyens — Fri, 10 Sep 2010 19:16:14 GMT

Hello Kumar,

Make sure you are loading the name of the field properly, as fields are case sensitive (order_date is not equal to Order_Date). As the field has the underscore, make sure also that it doesn't have to be enclosed in some way.

Did you try the sql query as it is and it returned results?

Regards.

Script Error - Field not found

Fri, 10 Sep 2010 19:20:09 GMT

You change the case of the "d" in order_date on the 3rd and 4th fields in the load:

order_Date - yearstart(order_Date) + 1 AS OrderDayWithinYear,
order_Date - monthstart(order_Date) + 1 AS OrderDayWithinMonth

should be

order_date - yearstart(order_date) + 1 AS OrderDayWithinYear,
order_date - monthstart(order_date) + 1 AS OrderDayWithinMonth

so that the case is the same as the SQL select

Script Error - Field not found

Fri, 10 Sep 2010 19:23:45 GMT

I changed it to the following:

Orders_Tab:
LOAD
order_date,
date(floor(order_date)) AS link_Date,
date(floor(order_date)) AS Order_Date, //Load it a second time for making date range selections

order_date - yearstart(order_date) + 1 AS OrderDayWithinYear,
order_date - monthstart(order_date) + 1 AS OrderDayWithinMonth,

OID as OrderId,
scp_oid as SCP_Id,
entity_oid as Entity_Id;

SQL
SELECT oid,trunc(ot.order_date) order_date,ot.scp_oid scp_oid,ot.entity_oid entity_oid from orders_tab ot
where TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY')
AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') and ot.order_status>1;

Still getting the same error...

Script Error - Field not found

llauses243 — Fri, 10 Sep 2010 19:24:02 GMT

Hi,

Wait help you ...

When I use select with AS400 upper or lower case no important, but at load must be upper case

Then to CHANGE order_date by ORDER_DATE in script, then test

good luck, Luis

Script Error - Field not found

Fri, 10 Sep 2010 19:30:38 GMT

Thanks for your inputs Miguel & Cowboy: I dint know that the script was case sensitive

Thanks IIauses: Your trick worked...

Script Error - Field not found

Fri, 10 Sep 2010 19:39:19 GMT

As a side note and not really related to the issue but if you are using the pl/sql function trunc() on your date then you shouldnt really need the QV floor() function in the load as trunc by default should only return the date, seems like double work.