topic Re: Comparing filetime vs now time in QlikView

Comparing filetime vs now time

JohnSamuel123 — Wed, 17 Feb 2021 15:03:02 GMT

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 🙂 )

Re: Comparing filetime vs now time

QFabian — Wed, 17 Feb 2021 15:09:32 GMT

Hi @JohnSamuel123 , please try this :

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

Re: Comparing filetime vs now time

JohnSamuel123 — Wed, 17 Feb 2021 15:18:58 GMT

Hi @QFabian ,

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

thanks,

john

Re: Comparing filetime vs now time

QFabian — Wed, 17 Feb 2021 17:25:34 GMT

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

Re: Comparing filetime vs now time

JohnSamuel123 — Thu, 18 Feb 2021 09:12:47 GMT

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

Re: Comparing filetime vs now time

marcus_sommer — Thu, 18 Feb 2021 10:47:09 GMT

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

Re: Comparing filetime vs now time

JohnSamuel123 — Thu, 18 Feb 2021 11:27:10 GMT

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