Data format unknown seconds with decimals

[Anonymous]

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

[Anonymous]

I forgot the attachment. Find it below.

[swuehl · Accepted solution]

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

[Anonymous]

Thanks a lot for your help Stefan, thats it.

Kind regards,

Henco

[Anonymous]

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

[swuehl]

Hi,

try

...

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

...

[Anonymous]

Thanks again, you helped me out.

Cheers,

Henco

[rwunderlich]

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

[Anonymous]

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