topic Re: Loading dates directly from the database to Qlikview in QlikView

Loading dates directly from the database to Qlikview

Anonymous — Tue, 30 Jul 2013 01:59:21 GMT

I have a Oracle table as below

Name

Null

Type

----------------------- -------- -----------

MFG_ORDER_NUM

NOT NULL NUMBER(9)

ORDER_STATUS_SQUNC_NUM NOT NULL NUMBER(3)

ORDER_STATUS_ID	NOT NULL VARCHAR2(6)
REASON_CODE_ID	VARCHAR2(6)
ORDER_STATUS_OPER_ID	VARCHAR2(6)
ORDER_STATUS_TMSTMP_OUT	DATE
ORDER_STATUS_TMSTMP_IN	DATE

the two date feilds holds data as 03-may-07 10:05:00 with date and time stamp .

I need to store this date as month, day , year , hour , am loading the table directly from oracle database .

am using the below simple sql statement to fetch the data into qlikview. how can i modify this statement to get the dates as splited ones into the application.

SELECT mfg_order_num , order_status_id , Order_Status_Tmstmp_In , order_status_tmstmp_out from torder_status ;

Re: Loading dates directly from the database to Qlikview

tresB — Tue, 30 Jul 2013 04:15:26 GMT

try this:

LOAD *,
Date(Order_Status_Tmstmp_In) as In_Date,
Year(Order_Status_Tmstmp_In) as In_Year,
Month(Order_Status_Tmstmp_In) as In_month,
Day(Order_Status_Tmstmp_In) as In_Day,
WeekDay(Order_Status_Tmstmp_In) as In_Weekday,
Hour(Order_Status_Tmstmp_In) as In_hour,

Date(Order_Status_Tmstmp_out) as out_Date,
Year(Order_Status_Tmstmp_out) as out_Year,
Month(Order_Status_Tmstmp_out) as out_month,
Day(Order_Status_Tmstmp_out) as out_Day,
WeekDay(Order_Status_Tmstmp_out) as out_Weekday
Hour(Order_Status_Tmstmp_out) as out_hour
;

SELECT mfg_order_num , order_status_id , Order_Status_Tmstmp_In , order_status_tmstmp_out from torder_status ;

Re: Loading dates directly from the database to Qlikview

Anonymous — Tue, 30 Jul 2013 04:59:13 GMT

Field not found - <Order_Status_Tmstmp_In>
SELECT mfg_order_num , order_status_id , Order_Status_Tmstmp_In , order_status_tmstmp_out from torder_status

I get this error when i try the above statement .. the select statements before the load works.

it errors out saying the above when it gets to the load statement.

i have the connection string open and able to load other data from the database ..

am i missing something ?