Date matching with Excel

JaDuenas — Fri, 15 Nov 2024 23:20:40 GMT

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?

topic Date matching with Excel in QlikView

Date matching with Excel