topic Strange behavior with Excel dates in QlikView

Strange behavior with Excel dates

Mon, 04 Mar 2013 11:48:24 GMT

Hi,

When I load the following dates from Excel, somehow the field Started

is not formatted as expected.
Both fields look identical in Excel.

Only if I add it as a new field (with the date#) it works properly.

Please see attached images.

The problematic script:

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as Started,
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as Finished

FROM
[..\Data\Sales Orders - Durations.xls]
(biff, embedded labels, table is [Sales Orders - Durat$]);

The script that works:

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as NewStarted,
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as Finished,
....

Thanks!

Re: Strange behavior with Excel dates

manideep78 — Mon, 04 Mar 2013 11:52:11 GMT

Right Click on the Table--> Properties--> Number tab-->

Change the option as TimeStamp#

Re: Strange behavior with Excel dates

er_mohit — Mon, 04 Mar 2013 11:53:21 GMT

hiii

it give you the number format of date generates by OS..

by changing the alias name you got the right format which you want like

LOAD [Case ID],
Events,
date#(Started, 'DD.MM.YYYY hh:mm:ss') as A_Started,
Date#(Finished, 'DD.MM.YYYY hh:mm:ss') as c_Finished

FROM
[..\Data\Sales Orders - Durations.xls]
(biff, embedded labels, table is [Sales Orders - Durat$]);

Re: Strange behavior with Excel dates

Mon, 04 Mar 2013 11:58:44 GMT

Hi,

The question is why the fields don't behave in the same manner.

Another words, one can be directly manipulated, and the other one needs a name change.

Thanks!

Re: Strange behavior with Excel dates

er_mohit — Mon, 04 Mar 2013 12:31:23 GMT

if you see your field finished, it can't be give you the right format which you want somehow it gives you default format as your OS. and sometimes it gives the autogenerate number which show in your

date#(Date(Started, 'DD.MM.YYYY hh:mm:ss'),'DD.MM.YYYY hh:mm:ss') as Started

try this

because date# always work with date function so, date# gives you the format code and date gives you the format you want