Skip to main content
Marching toward a simplified navigation! READ ON
Showing results for 
Search instead for 
Did you mean: 

Date matching with Excel


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?


Labels (3)
0 Replies