Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

igdrazil
Contributor III

Qlik Sense omitting Milliseconds

Hi Community,

I have a requirement for my current app to load the milliseconds of a time field, but for some reason qlik is omitting the milliseconds value. I've tried editing the variables in the Main tab in the loading script and also tried some shenanigans with the num#/timestamp# and time-functions.

Here's also an example of the database

Experiment NumberMachineDwell TimeWaiting Time
1100:01:02.527000:00:02.8145
1100:00:56.635400:00:02.4587
1100:00:42.458400:00:04.8537
1200:01:02.557400:00:03.6584

Usually i would ignore the milliseconds, but i have around 50 rows per machine, each nearly 1 second in milliseconds, which means i got a total difference of around 40 to 50 seconds.

Thanks in advance

Chris

10 Replies
avkeep01
Valued Contributor

Re: Qlik Sense omitting Milliseconds

Hi Christoph,

have you tried multiplying the timestamp? Like * 1000 makes seconds * 60 makes minutes etc.?

igdrazil
Contributor III

Re: Qlik Sense omitting Milliseconds

Yes, I've tried that, but with no success

Thanks for your reply

Chris

bwisealiahmad
Valued Contributor

Re: Qlik Sense omitting Milliseconds

Does this not work?

=timestamp(Value,'MM/DD/YYYY hh:mm:ss.fff')

igdrazil
Contributor III

Re: Qlik Sense omitting Milliseconds

Sadly it does not.

I'll get a timestamp with milliseconds displayed but they are all zeroes.

For now, I went into the database and switched the column type from time to text and then loaded the data again and it kind of worked. Not sure how it worked now tho.

If anyone got an idea why this works, please help me out.

Kind Regards

Chris

omarbensalem
Esteemed Contributor

Re: Qlik Sense omitting Milliseconds

try :

timestamp( timestamp#(Value,'MM/DD/YYYY hh:mm:ss.fff'),'MM/DD/YYYY hh:mm:ss.fff')

igdrazil
Contributor III

Re: Qlik Sense omitting Milliseconds

Your solution does not produce any outcome.

Skript:

2017-11-28 14_24_04-Qlik Sense Desktop.png

Data Model example values:

2017-11-28 14_25_22-Qlik Sense Desktop.png

Thanks for you reply anyway!

Regards

Chris

omarbensalem
Esteemed Contributor

Re: Qlik Sense omitting Milliseconds

than maybe:

times( times#(Value,'hh:mm:ss.fff'),'hh:mm:ss.fff')


it all depends on how your field is written by default in the first place

MVP
MVP

Re: Qlik Sense omitting Milliseconds

If you look at your table you really don't have milliseconds there. Milliseconds have only 3 digits - your table has 4 digits. So what these timestamps have is 100 microseconds precision.

If you have this table as an inline table in Qlik Sense it will actually allow you to have the full 100 microsecond precision and interpret them as timestamps. But there is a catch. The field will keep the 100 microsecond precision on the individual value level. However when you do calculations and display in Qlik Sense it will truncate the non-millisecond digit.

I suspect however that your data comes from a database and are being read from the database as a timestamp and then it might be that the ODBC-driver enforce an exact ISO timestamp which is only milliseconds and not with 100 microseconds precision - truncating the last of the four digits?

MVP
MVP

Re: Qlik Sense omitting Milliseconds

You can circumvent the ISO timestamp/datetime interpretation in Qlik Sense by making some adjustments in your load script. The W4 field will be a pure numeric value not being limited to just milliseconds but can have additional fractions of milliseconds too. When displaying calculations with W4 you can always format it specifically as timestamp and Qlik Sense will happily round them to the nearest millisecond.

I have examplified it in the attached Qlik Sense App:

LOAD SCRIPT:

2017-11-28 15_19_54-Qlik Sense Desktop.png

DATA MODEL VIEWER:

2017-11-28 15_20_25-Qlik Sense Desktop.png

SHEET:

2017-11-28 15_20_39-Qlik Sense Desktop.png