Hello,
I am trying to match data coming from Excel and from a No SQL database, the tiemstamps being my synthtic key.
I have converted UNIX format time from my db into TS using the following script:
My original time format "2021-01-04T14:00:00Z" which is converted by Qlik into the number of
milliseconds that have elapsed since January 1, 1970 00:00:00 (UTC)
My code is then the following:
date(timestamp(round(left([time],10))/ 86400 + 25569),'DD/MM/YYYY') as date,
time(timestamp(round(left([time],10))/ 86400 + 25569),'hh:mm:ss') as heure,
timestamp(round(left([time],10))/ 86400 + 25569,'DD/MM/YYY hh:mm:ss') AS TS,
On the other hand I have Excel file with standard Excel dates rounded to the millisecond (date_prev), which I convert into TS using the following script:
date(date_prev,'DD/MM/YYYY') as date,
time(date_prev,'hh:mm:ss') as heure,
timestamp(date_prev,'DD/MM/YYY hh:mm:ss') AS TS
Now, my TS are matching for certain hours but not all of them :
00:00 ok
00:01 ok
00:02 ok
00:03 NOT OK (I have two different rows for data coming from Excel and DB)
and so on: 07:00; 10:00; 11:00; 15:00; 19:00; 22:00 and 23:00 are not working. The rest are OK.
This happens for every day in my data.
Can somebody help me please?
