topic Re: Re: Export to Excel (Time format problem) in QlikView

Export to Excel (Time format problem)

sanderha — Tue, 02 Sep 2014 09:09:44 GMT

Hello,

I try to export a table box to Excel, but the Time fields aren't exported correctly.

Example: 8:40:00 (in Qlikview) --> -53322,6388888889 (in Excel).

Table Box Properties:

I use Qlikview 11.00.11282.0 and Office 2013.

Is there anyone who can help me with this?

Kind regards,

Sander

Re: Export to Excel (Time format problem)

Mattias_Malré — Tue, 02 Sep 2014 09:12:08 GMT

Moved this to the QlikView part of the community.

Re: Export to Excel (Time format problem)

ashfaq_haseeb — Tue, 02 Sep 2014 09:19:29 GMT

Hi,

You can try by changing it from time to timestamp.

If possible can you share sample app to look over.

Regards

ASHFAQ

Re: Export to Excel (Time format problem)

amit_saini — Tue, 02 Sep 2014 09:24:37 GMT

Sander,

Please share source data if possible.

Thanks,
AS

Re: Re: Export to Excel (Time format problem)

sanderha — Tue, 02 Sep 2014 09:41:34 GMT

I uploaded an attachment with sample data.

If I export this table to Excel I get this:

Re: Export to Excel (Time format problem)

vinay_hg — Tue, 02 Sep 2014 10:17:58 GMT

before changing the format in table property first convert the raw date field to time filed before loading by using Date#().

Re: Export to Excel (Time format problem)

v_iyyappan — Tue, 02 Sep 2014 10:31:58 GMT

Hi,

You can use Straight Table instead of Tablebox . Please check the attached file........

Regards,

Re: Export to Excel (Time format problem)

tresB — Tue, 02 Sep 2014 10:32:32 GMT

Your actual field values are negative generating timestamp beyond 30/12/1899 which is actually zero (0). Excel perhaps could not recognize such dates. Hence the issue. You might have to reload data properly formatted.

Re: Export to Excel (Time format problem)

ashfaq_haseeb — Tue, 02 Sep 2014 10:36:33 GMT

Hi,

As your dates are in 70's. QlikView is not properly converting dates or Excel does not recognize such dates..

Can you try to convert your start time and end time field into proper time while fetching data.

I tried to convert in qlikView and its not working somehow.

or may be Marco Wedel can come up with some fantastic expression.

Regards

ASHFAQ

Re: Export to Excel (Time format problem)

MarcoWedel — Tue, 02 Sep 2014 10:57:55 GMT

Instead of

FACT_Example:

LOAD "Date",

"Start Time",

"End Time";

SQL SELECT Date,

"Start Time",

"End Time"

FROM "NAV_O_POWER_SUPPORT".dbo."Support$KTN TimeregIT TimeRegistration"

WHERE ("Date">'2014-08-30 00:00:00.000' AND "Date" < getdate())

AND (Employee = 'HAECK');

maybe

FACT_Example:

LOAD "Date",

Time(Frac("Start Time")) as [Start Time],

Time(Frac("End Time")) as [EndTime];

SQL SELECT Date,

"Start Time",

"End Time"

FROM "NAV_O_POWER_SUPPORT".dbo."Support$KTN TimeregIT TimeRegistration"

WHERE ("Date">'2014-08-30 00:00:00.000' AND "Date" < getdate())

AND (Employee = 'HAECK');

hope this helps

regards

Marco

Re: Re: Export to Excel (Time format problem)

ashfaq_haseeb — Tue, 02 Sep 2014 11:05:14 GMT

Yes,

Frac did the job.

Have a look at attached application with solution.

Regards

ASHFAQ

Re: Re: Export to Excel (Time format problem)

sanderha — Tue, 02 Sep 2014 11:24:49 GMT

Indeed, Frac works.

Thank you!