topic Re: Timestamp presision in QlikView

Timestamp presision

yura_ratu — Wed, 06 Jan 2016 15:57:36 GMT

Hi Community,

We have the following date format in database

05-JAN-16 19.07.23.933825

When I try to organize incremental load and find max value I've faced an issue when Qlikview cut milliseconds after 3rd figure

See the following expression

=Timestamp(Timestamp#('05-JAN-16 19.07.23.933825','DD-MMM-YY hh.mm.ss.ffffff'),'DD-MMM-YY hh.mm.ss.ffffff')

the result will be

05-Jan-16 19.07.23.933000

So that I can't accurately compare such dates, because 3 last figures have been lost

Any ideas how to solve it?

Re: Timestamp presision

maxgro — Wed, 06 Jan 2016 16:24:54 GMT

I had the same problem some months ago with an Oracle timestamp. More decimal in Oracle than in Qlik. I wasn't able to solve at the Qlik side, so the workaround I found was:

In Qlik I stored the last Oracle timestamp as text, no figures was lost. The timestamp was converted to char at the Oracle side (TO_CHAR).

In the incremental I converted (at the Oracle side, TO_TIMESTAMP) the timestamp stored in Qlik as text

back to an Oracle timestamp.

Re: Timestamp presision

sunny_talwar — Wed, 06 Jan 2016 16:25:09 GMT

There are some links where people have tried to do it (and it seemed it worked for them), but I was unable to replicate the same expression for yours. May be you can figure something out:

How to format Qlikview Timestamp upto microseconds?‌

http://stackoverflow.com/questions/28853999/qlikview-timestamp-formatting-upto-microseconds

Re: Timestamp presision

marcus_sommer — Wed, 06 Jan 2016 16:31:46 GMT

What if if you used a pure number like: num(timestamp#(YourField, YourFormat))? Whereby normally should qv be able to handle a number like this although it has restrictions by large numbers respectively a lot of digits.

Another possibility might be to use two fields - one for dates and one for times for your incremental logic and for your datamodel itself, then: The Importance Of Being Distinct

- Marcus

Re: Timestamp presision

yura_ratu — Wed, 06 Jan 2016 16:41:53 GMT

Hi Marcus,

I tried to do so, but it didn't work. If put this expression to the text box

=num(Timestamp#('05-JAN-16 19.07.23.933825','DD-MMM-YY hh.mm.ss.ffffffffff'),)
&chr(10)
&num(Timestamp#('05-JAN-16 19.07.23.933826','DD-MMM-YY hh.mm.ss.ffffffffff'),)

There will two same numbers, however there is a difference in microseconds

Re: Timestamp presision

rbecher — Wed, 06 Jan 2016 16:45:01 GMT

Hi Yurii,

do you need this time precision at all? If not, you can round or cut it of before comparing.

- Ralf

Re: Timestamp presision

marcus_sommer — Wed, 06 Jan 2016 17:00:23 GMT

Try it with two or maybe three fields, like:

subfield(YourField, ' ', 1) as DateString

mid(subfield(YourField, ' ', 2), 1, 10) as TimeString

mid(subfield(YourField, ' ', 2), 11) as MillisecondString

and then converting it in numbers and if you need it you could simply add those fields.

- Marcus

Re: Timestamp presision

yura_ratu — Wed, 06 Jan 2016 18:02:26 GMT

Thanks, it helps in this particular scenario. However it's sad there is no way to deal with microseconds within Qlikview, without workarounds like you and Marcus proposed bellow.

Re: Timestamp presision

simonaubert — Thu, 05 Nov 2020 15:28:37 GMT

Hello,

You can vote on this idea : https://community.qlik.com/t5/Ideas/Qlik-Timestamp-precision-go-to-the-nanesecond-equivalent-to-DB/idi-p/1758948

Best regards,

Simon