Comparing filetime vs now time

[JohnSamuel123]

hi all,

im new to qlik,

im pulling in the filetime() of my xlsx files, i want to compare that vs the "now()" time and if the filetime() is greater than 10 minutes old, the script will fail,

what ive got so far 

"

test:

load *, filetime() from xlsx; 

IF filetime(test) >= timestamp(now()-10/1440) then

drop table Test1;

end if

"

my table "Test1" does not exist, this is my forced fail. however my script will not fail . i went into debug and i noticed that in my if statement it doesn't pick up the filename:

any information would be appreciated on how to get this working(or failing 🙂 ) 

[QFabian]

Hi @JohnSamuel123 , please try this :

filetime('..\Desktop\' & FileName()) as filetime

[JohnSamuel123]

Hi @QFabian ,

is this in the load statement or in the if statement for my () in filetime? 

thanks,

john

[QFabian]

should be something like this  :

test:

load *,

filetime('..\Desktop\' & FileName()) as filetime

from xlsx; 

Let vFileTime = peek('filetime',1, 'test');

IF $(vFileTime ) >= timestamp(now()-10/1440) then

drop table Test1;

end if

[JohnSamuel123]

hi @QFabian thanks you very much!

thats very close to what i need, however when i try to run it it doesn't look it is pulling in the variable vFileTime? 

any ideas as to why this is happening? is it because it is a date/time field? if i converted it to just a time or a number field i wonder if it would work?

thanks again,

John

[marcus_sommer · Accepted solution]

You may try it with:

if '$(vFileTime)' >= ...

because it takes here the string-interpretation from the value. Usually better is to use always pure numeric values for any matching/calculating which avoids troubling with the formatting-stuff. In your case it means something like: num(now()) and not timestamp(now()).

Important is that num() didn't returned a comma as decimal-delimiter which would work fine in all normal field / UI calculations but not by assigning/calling it to a variable. If the comma is the default delimiter you could change it with the second and third parameter of num().

- Marcus

[JohnSamuel123]

thanks both, yes @marcus_sommer  youre right, i will just convert it to a num as it was reading it as a str. thanks for your help! 

i have it working now with the following: 

let vTime = num((peek('filetime',1, 'test')));

if vTime <= num(now()-10/1440) then
drop table Test1;

end if