topic Data format unknown seconds with decimals in QlikView

Data format unknown seconds with decimals

Anonymous — Wed, 07 Dec 2011 18:49:28 GMT

Hi there,

I am developing Google Analytics into QlikView by using the Data Feed Query Explorer.

There is a dimension I use called Average Time On Site. This field is returned as a string in seconds, but QV doesn't recognize that as seconds or a number.

In the attachment you can find the result per date. I don't know how to change this format into a number so I can use it in an expression. Any thoughts?

Kind regards,

Henco

Data format unknown seconds with decimals

Anonymous — Wed, 07 Dec 2011 18:52:23 GMT

I forgot the attachment. Find it below.

Data format unknown seconds with decimals

swuehl — Wed, 07 Dec 2011 19:41:30 GMT

What is your standard decimal point separator set to? Or how do you parse in the number while loading in?

Have you tried

LOAD

Date,

num#(AvgTimeOnSite, '#.#' ,'.',',') as AvgTimeOnSite

from ...

so explicitely stating the decimal point separator.

Hope this helps,

Stefan

Data format unknown seconds with decimals

Anonymous — Wed, 07 Dec 2011 21:34:46 GMT

Thanks a lot for your help Stefan, thats it.

Kind regards,

Henco

Data format unknown seconds with decimals

Anonymous — Wed, 07 Dec 2011 22:50:50 GMT

Could you also tell me how to put this in HH:MM:SS format?

It doesn't work when I choose Interval or Time on the Number tab.

Henco

Data format unknown seconds with decimals

swuehl — Wed, 07 Dec 2011 23:04:33 GMT

Hi,

try

...

interval(num#(AvgTimeOnSite,'.',',')/(24*60*60),'hh:mm:ss') as AvgTimeOnSiteFormatted

...

Data format unknown seconds with decimals

Anonymous — Wed, 07 Dec 2011 23:20:17 GMT

Thanks again, you helped me out.

Cheers,

Henco

Re: Data format unknown seconds with decimals

rwunderlich — Thu, 08 Dec 2011 03:42:23 GMT

Computing the time with division may be accurate enough for your app, but it can cause problems. The more accurate QV way to convert seconds into an interval is:

interval#(secondsValue, 's')

If you do it with division, your intervals may not match up with intervals from QV functions or data from other sources like DB tables. This can become a problem when for example you assign your time values to buckets.

For more, see

http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html

-Rob

Data format unknown seconds with decimals

Anonymous — Fri, 09 Mar 2012 21:39:25 GMT

Hi all,

Maybe one of you knows an answer to my question? I hope so.

See http://community.qlik.com/message/199424#199424

Regards,

Henco