Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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?
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
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.
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