topic Issue Converting Qlikview Time Interval Format to Excel in QlikView

Issue Converting Qlikview Time Interval Format to Excel

Fri, 11 Feb 2011 10:11:30 GMT

I'm having difficulty when exporting a QV object to Excel. The Expression in question is of type Interval dd:hh:mm format but when coverting to Excel, any value greater than 31 days seems to be truncated down to a value 31 days or under.

Additionally, excel can't seem to handle negative Time Intervals and expresses them as '#########'.

The format I'm using in excel is Custom dd:hh:ss but it's obviously not working.

Any help would be greatly appreciated.

Regards,

Jim.

Issue Converting Qlikview Time Interval Format to Excel

Tue, 15 Feb 2011 13:22:03 GMT

Any ideas?

Issue Converting Qlikview Time Interval Format to Excel

Tue, 15 Feb 2011 13:25:23 GMT

Can you please share application with that object from which you want to export to excel

Regards--

Nayan

Issue Converting Qlikview Time Interval Format to Excel

Tue, 15 Feb 2011 13:31:59 GMT

Hi,

Sorry but I'm not familiar with how to share an application with an object. Can you provide some brief steps please?

Regards,

Jim.

Re: Issue Converting Qlikview Time Interval Format to Excel

Tue, 28 May 2013 07:55:41 GMT

Hi...

Has there been any solution to this...

Please suggest..

Thanks in advance...

Re: Issue Converting Qlikview Time Interval Format to Excel

Tue, 28 May 2013 08:02:28 GMT

IN QLIKVIEW

write your datefield as

date(date#(datefield,'DD/MM/YYYY'),'MM/DD/YYYY')

HERE bold format set according to your excel format. then export as excel

hope this helps you

Regards

vishwaranjan

Re: Issue Converting Qlikview Time Interval Format to Excel

Wed, 29 May 2013 05:54:20 GMT

Hi Ranjan...

It is actually a negative time interval represented in QlikView using Interval() and is correct, e.g., -03:00 which means -3 hours. However, on exporting to excel, it is shown as some negative value like -0.25 something but in the actual cell value it is like '#############'.

So, is there any way in QlikView to convey to excel with which excel can depict the negative time interval in appropriate format??

Thanks and Regards,

Re: Issue Converting Qlikview Time Interval Format to Excel

Wed, 29 May 2013 06:51:47 GMT

try to write your interval() within NUM#() then export in excel.

or text() function

example-- =Num#(interval('02/02/13' - '02/03/13'))

example-- =Text(interval('02/02/13' - '02/03/13'))

Re: Issue Converting Qlikview Time Interval Format to Excel

marcus_sommer — Wed, 29 May 2013 11:53:45 GMT

It is an excel feature that negative time-values are not possible, unless you changed global excel-settings whereby all date-values changed (not really recommended).

Only for display you could convert in excel this value in a string and hid this colums. All another calculation must be on the originally columns.

if(time<0;"-"&text(time * -1, "hh:mm:ss");time)

- Marcus