Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Any ideas?
Can you please share application with that object from which you want to export to excel
Regards--
Nayan
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.
Hi...
Has there been any solution to this...
Please suggest..
Thanks in advance...
K
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
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,
K
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'))
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