Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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
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
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
Hi Yurii,
do you need this time precision at all? If not, you can round or cut it of before comparing.
- Ralf
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
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.
Hello,
You can vote on this idea : https://community.qlik.com/t5/Ideas/Qlik-Timestamp-precision-go-to-the-nanesecond-equivalent-to-DB/i...
Best regards,
Simon