topic Re: Including tenths/hundredths of second in Time in QlikView

Including tenths/hundredths of second in Time

Tue, 17 Nov 2015 21:42:55 GMT

Looked at all references to Time and Time# and could only find 'hh:mm:ss tt' documented. Excel provides for tenths, hundredths, etc. of a second (e.g. "mm:ss.0"). When I do the same with Qlik only those values having zero tenths get converted (as if the zero were a filter!?).

Any suggestions (or workarounds) on how to accomplish displaying tenths/hundredths of a second. This time precision is important in my application and life would be easier to keep the field as time rather than (say) convert the field to # of tenths of a second and figure out how to reconstruct a display field.

My application compares event times and yes tenths (and in some cases hundredths) of a second are important.

Help Time gurus! (Thanks in advance.)

Re: Including tenths/hundredths of second in Time

sunny_talwar — Tue, 17 Nov 2015 22:05:08 GMT

Not sure if this would help, but may be this:

Time(TimeField, 'hh:mm:ss ttt') // with one extra t

Time(TimeField, 'hh:mm:ss.ffff')

May be you have already tried it and did not work, but I thought it might help to add that there

Re: Including tenths/hundredths of second in Time

swuehl — Tue, 17 Nov 2015 22:12:09 GMT

As Sunny said, using the correct format code should enable interpreting a timstamp including up to milliseconds.

How do your input records look like? Could you post some samples of that timestamp?

Re: Including tenths/hundredths of second in Time

MarcoWedel — Wed, 18 Nov 2015 01:08:57 GMT

Hi,

I don't find any issues with the default interpretation of time values including tenth, hundreds or thousandth of a second:

SET TimeFormat='hh:mm:ss';

tabTimes:
LOAD *,
    Text(yourtime) as yourtimetxt,
    Time#(yourtime, 'hh:mm:ss') as yourtime2,
    Time#(yourtime, 'hh:mm:ss.fff') as yourtime3,
    Time#(yourtime, 'hh:mm:ss[.fff]') as yourtime4
Inline [
yourtime
12:34:56
12:34:56.0
12:34:56.1
12:34:56.2
12:34:56.11
12:34:56.12
12:34:56.111
12:34:56.112
];

Like Stefan already asked for, please share a sample of your data to test with.

thanks

regards

Marco

Re: Including tenths/hundredths of second in Time

MarcoWedel — Wed, 18 Nov 2015 01:23:21 GMT

loading from excel you might have to use a format like

Time(yourtime, 'hh:mm:ss.fff')

SET TimeFormat='hh:mm:ss';

tabTimes:
LOAD A as yourtime,
    Text(A) as yourtimetxt,
    Time(A, 'hh:mm:ss') as yourtime2,
    Time(A, 'hh:mm:ss.fff') as yourtime3,
    Time(A, 'hh:mm:ss[.fff]') as yourtime4
FROM [..\Daten\QlikView\QlikCommunity\QlikCommunity_Thread_191833.xlsx] (ooxml, no labels, table is Tabelle1);

hope this helps

regards

Marco

Re: Including tenths/hundredths of second in Time

rwunderlich — Wed, 18 Nov 2015 01:29:42 GMT

'f' is the format code for fractions of a second. 'fff' would be thousands.

I do see it in the Help under "Format Codes"

To describe the fractions of a second, use the symbol "f" for each digit.

-Rob

Re: Including tenths/hundredths of second in Time

swuehl — Wed, 18 Nov 2015 11:33:47 GMT

Yes, need to get my eyes checked. I swear that 't' and 'f' looked identical on the small screen yesterday.

Re: Including tenths/hundredths of second in Time

sunny_talwar — Wed, 18 Nov 2015 11:34:36 GMT

I did not realize that I was typing ttt instead of fff

Re: Including tenths/hundredths of second in Time

Wed, 18 Nov 2015 14:29:16 GMT

Thanks to the community, marked as correct the first response of many!

Never in my wildest dreams did I think the community would tell me to '.f'' it -- and that I'd thank them for it ;^) (hopefully the humor isn't too off-color for the forum; apologies in advance if it is)