Compare timestamps in script not working

[david_kesselhei]

Hi,

I wrote a Qlik script that checks a qvd file for the timestamp of the newest data the qvd file has:

LastInsourceDateTable:
        Load Max("~insource_date") as LastInsourceDate
        From $(vQVDPath)$(vCountryName).qvd (qvd);
        LET vLastExecDate=peek('LastInsourceDate',0,'LastInsourceDateTable');
        LET vLastExecDate=TimeStamp('$(vLastExecDate)', '$(vTimeStampFormat)');

It then checks the database if there is newer data:

 NewestInsourceDateTable:
    LOAD
    *;
    SQL SELECT 
    max("~insource_date") as NewestInsourceDate
    FROM $(vFactTable)
	where [~Country]='$(vCountryName)'
    ;
    LET vNewestExecDate=peek('NewestInsourceDate',0,'NewestInsourceDateTable');
    LET vNewestExecDate=TimeStamp('$(vNewestExecDate)', '$(vTimeStampFormat)');

If the timestamp from the database is newer then the table should be refreshed:

If (vLastExecDate<vNewestExecDate) then
        Trace There is newer data for '$(vCountryName)';
end if

The problem is, when the timestamps are exactly the same, the table is still refreshed.

Example:

vLastExecDate '2019-05-24 08:30:06.000'
vNewestExecDate '2019-05-24 08:30:06.000'

How come the table is still reloaded even if the "<" isn't satisfied?

[JordyWegman]

Hi David,

Maybe try this, it's for hours and minutes, but maybe it will work.

LET vLastExecDate= time(frac(floor($(vLastExecDate),1/1440)),'hh:mm') 

LET vNewestExecDate= time(frac(floor($(vNewestExecDate),1/1440)),'hh:mm') 

Jordy

Climber

[david_kesselhei]

Thanks,

I tried it but it fails, unfortunately:

The error occurred here:
LET vLastExecDate= time(frac(floor(2019-05-24 >>>>>>08<<<<<<:30:06.000,1/1440)),'hh:mm')

Is the floor() supposed to get the datetime without the seconds?

As a side note: Even without the error, wouldn't that mean that if I ran the script at 23:59 I could never update the script again because it only looks at the hours and minutes?

[JordyWegman]

I think we need another approach. I would suggest that we make a float of the day/timestamp. For example: 43101.012020

This way you can more easily compare the numbers with each other. 

For the date you can use the floor(Date) to get the 43101 integer. Then you can use the frac for getting the second part. Add these two together and you will have your number. Do this for both variables and you can compare them.

This way you don't have the problem of the hh:mm, that is correct what you mentioned.

Jordy
Climber

[david_kesselhei]

Thanks for the help!

I tried that already, I think at it turned out like this:

LET vLastExecDateNum = Num(vLastExecDate);
LET vNewestExecDateNum = Num(vNewestExecDate);
Trace vLastExecDateNum   $(vLastExecDateNum);
Trace vNewestExecDateNum $(vNewestExecDateNum);    

LET vDiff = vLastExecDateNum-vNewestExecDateNum;
Trace vDiff $(vDiff);

vLastExecDateNum   43609,354247685

vNewestExecDateNum 43609,354247685

vDiff -1,8917489796877e-10

 So according to qlik, even these two numbers that are one are somehow not the same. I have no clue where the "-1,0913936421275e-10" could even come from. I also tried rounding to two decimal places: Still the same.

[JordyWegman]

No problem, it seems that they are different, maybe on miliseconds.. 

But if you have these numbers, you can go on with your comparison right? The If statement should work now.

Jordy
Climber