Suggest an Idea

[simonaubert]

Hello all,

On Qlik, Timestamp precision goes to the milisecond. That may seems sufficient but it's not. On many database,  Timestamp precision goes to the nanosecond.

On Oracle :
https://docs.oracle.com/database/nosql-12.1.4.3/SQLForNoSQL/timestamp.html
"Timestamp values have a precision in fractional seconds that range from 0 to 9. For example, a precision of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds, and 9 means a precision of nanoseconds. 0 is the minimum precision, and 9 is the maximum. "

On Hive :
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps...

Why does it matter? Do you really need such an amazing precision for analysis? Come on !

Incremental load, that's why !


You get a load with a record at 2020-11-03 01:02:03.123456789
Qlik Sense stores 2020-11-03 01:02:03.123

Next load=> You load every record where TS> 2020-11-03 01:02:03.123

Same record is here !!


Oh, you gonna say : just round the TS like where (TS with a milisecond)>2020-11-03 01:02:03.123.

Well that does not work either if you have a record at 2020-11-03 01:02:03.1239999.

There is, however a turnaround : going full text with something like that

LOAD LCC_MAJ_DT,

            LCC_MAJ_DT_TIMESTAMP,

            LCC_LIGNE_ID,

    LCC_LIGNE_DETAIL_ID

    ;

SQL SELECT

            LCC_MAJ_DT as LCC_MAJ_DT_TIMESTAMP,

    to_char(LCC_MAJ_DT,'YYYY_MM_DD HH24:MI:SS.FF') as LCC_MAJ_DT,

            to_char(LCC_LIGNE_ID) as LCC_LIGNE_ID,

    to_char(LCC_LIGNE_DETAIL_ID) as LCC_LIGNE_DETAIL_ID

FROM LSNTER."DIS_F_CC_LIGNE" where LCC_MAJ_DT>TO_TIMESTAMP('$(t_date)','YYYY_MM_DD HH24:MI:SS.FF')

;

(edit : do not use YYYY-MM-DD HH24:MI:SS.FF=>> Qlik will understand it's a timestamp and stupidly recast it to TS)

But this is quite inefficient when you have to do that on hundred of millions of line ! And also you have to store it in a string format in qvd.. costly.

[Meghann_MacDonald]

Very entertaining read 🙂

Meghann

[simonaubert]

@Meghann_MacDonaldThanks 😉 I spent hours finding a way to have a turnaround perfectly working (we have a very industrial extract system on Qlik...) so I had to have at least some rest writing that idea.

[Patric_Nordstrom]

Thank you for your feedback on ways to improve our product. While this is something we understand would be useful, it's not on the short-term roadmap. Please continue to show your support for this idea.

Thanks,

Patric

[Olivier_W]

I agree. Great job Simon

[ab9503]

I hit the exact same problem that the OP describes, and a websearch led me to this idea. Yes please please implement it. Until then, incremental data loads are inefficient and a pain!

(In the meantime, @simonaubert , thanks very much for your text-based workaround.)

[simonaubert]

Hello @ab9503  it's a pleasure 😉

[simonaubert]

FYI, Alteryx now goes to the nanoseconds (fresh news, with 2023.1 released yesterday 😉  )
Qlik, will you try to catch it ? 😉

[ab9503]

FYI, to help mitigate inefficiencies, instead of storing strings I'm storing decimals.

UPDATE: The "=" comparison would sometimes fail. I found that the floating values were less precise than they appeared. (When I multiplied by 10000000000 I found unexpected digits beyond the nine "displayed" decimals.) So the following num#(left(text(...))) acrobatics are necessary to get both numbers down to just the real digits. (Of course, that further whittles away at decimal's efficiency gains. Ugh. C'mon, Qlik!)

tmp: Load max(theTimestamp) as Max_QlikRounded_Time
     From FILE.QVD (qvd);
Let vMax_RealDigits_Time = num#(left(text(Peek('Max_QlikRounded_Time')), 15), '0.0');
Drop Table tmp;

tmp: Load max(theSeconds_of_theTimestamp) as ActualSecondsForTheTime
     From FILE.QVD (qvd)
     Where num#(left(text(num(theTimestamp)), 15), '0.0') = $(vMax_RealDigits_Time);
Let vActualSecondsForTheTime = Peek('ActualSecondsForTheTime');
Drop Table tmp;

Let vMaxTimeString = Timestamp(vMax_QlikRounded_Time, 'YYYY-MM-DD hh:mm:') & Num(vActualSecondsForTheTime, '00.0000000');

theLatest:
Load *;
SQL SELECT *
         , try_parse(format(theTimestamp, 'ss.fffffff') AS decimal(9,7))
           AS theSeconds_of_theTimestamp // The "next" dataload will use this.
                                         // (See the evaluation at top.)
FROM myTable
WHERE theTimestamp > '$(vMaxTimeString)';

Concatenate(theLatest)
Load * From FILE.QVD (qvd);

STORE theLatest INTO FILE.QVD (qvd);

Drop Table theLatest;

Qlik, please keep time as precisely as the major DB vendors, so that we don't need these inefficient workarounds.

[Meghann_MacDonald]

From now on, please track this idea from the Ideation portal. 

Link to new idea

Meghann

NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you only see 1 tab with the login page, please try clicking this link first: Authenticate me! then try the link above again. Ensure pop-up blocker is off.