Qlik Sense omitting Milliseconds

[igdrazil]

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 Number	Machine	Dwell Time	Waiting Time
1	1	00:01:02.5270	00:00:02.8145
1	1	00:00:56.6354	00:00:02.4587
1	1	00:00:42.4584	00:00:04.8537
1	2	00:01:02.5574	00: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

[avkeep01]

Hi Christoph,

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

[igdrazil]

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

Thanks for your reply

Chris

[bwisealiahmad]

Does this not work?

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

[igdrazil]

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 · Accepted solution]

try :

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

[igdrazil]

Your solution does not produce any outcome.

Skript:

Data Model example values:

Thanks for you reply anyway!

Regards

Chris

[OmarBenSalem]

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

[petter]

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?

[petter]

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:

DATA MODEL VIEWER:

SHEET: