topic Re: How to check if a QVD file exists when it's name isn't fixed? in QlikView

How to check if a QVD file exists when it's name isn't fixed?

Fri, 19 Jun 2015 16:11:42 GMT

As part of my incremental load process, I am loading in a number of smaller QVD files into other QVD files.

The small files are created with a date string on the end of the filename.

before attempting to load the small files, I would like to check to see if any exist, so it can fail gracefully.

The examples I have found use the QvdCreateTime() function to check if a QVD file exists, but this needs a specific filename passed in.

Does anyone have a way of checking to see if any files exist when the filename is not fixed.

ie a file created today at midday would be TABLENAME_20150619120000.qvd

Thanks

Mark

Re: How to check if a QVD file exists when it's name isn't fixed?

giakoum — Fri, 19 Jun 2015 16:14:50 GMT

if you use a for loop like the one bellow, neither if it exists nor the name matters :

FOR EACH File in FileList('yourshare\*.qvd')

Re: How to check if a QVD file exists when it's name isn't fixed?

swuehl — Fri, 19 Jun 2015 16:15:53 GMT

You can use something like

Let vQVDExist = 0;

For each File in Filelist('TABLENAME_*.qvd')

Let vQVDExist = 1;

Next File

Re: How to check if a QVD file exists when it's name isn't fixed?

ramoncova06 — Fri, 19 Jun 2015 16:36:36 GMT

the problem with using a "*" when reading files is that this will give you all files that exist in that folder for that table name,

so unless you move the qvd's after you process them you will keep adding them in.

Going back to the qvdcreatetime, you are able to add to a dynamic file name

let vQVDTime = Now()

let vControlQVD = 'TABLENAME_$(vQVDTime ).qvd';

let vControlQVDPathCreationDate = filetime( '$(vControlQVD)' );

if len('$(vControlQVDPathCreationDate)') > 0 then

whatever you want to do

end

Re: How to check if a QVD file exists when it's name isn't fixed?

Fri, 19 Jun 2015 16:44:07 GMT

Thanks for the replies,

to clarify the question a little bit - all I need is establish if a file of type TABLENAME_*.qvd exists before using the LOAD * from TABLENAME_*.qvd syntax.

Using a hybrid of the answers above, I came up with:

// Check there are some files to load...

let vFileExists = 0;

For each File in ('$(vDataPath)$(vTableName)_*.qvd')

let vFileExists = 1;

exit for when $(vFileExists) = 1;

NEXT File

if $(vFileExists) = 1 then

LOAD * from $(vDataPath)$(vTableName)_*.qvd;

else

TRACE No Files to Load;

end if;

However, this does not seems to work. Does the For loop always execute? I would have thought it would only enter the body of the loop if the filelist had some files in?

Thanks

Mark

Re: How to check if a QVD file exists when it's name isn't fixed?

Gabriel — Fri, 19 Jun 2015 16:44:10 GMT

Hi,

Try doing something like this

IF(FileSize(FileSource\TABLENAME_20150619120000.qvd) )> 0 THEN

Do whatever you want when the file exist

else

Do whatever you want if the file doesn't exist..

ENDIF

Re: How to check if a QVD file exists when it's name isn't fixed?

Fri, 19 Jun 2015 16:48:07 GMT

Hi Thanks for the suggestion.

I am indeed moving processed files into a separate folder later on in the load script, so * is safe enough at this point.

It is very unlikely that a file will have been created for exactly the time that I am running this aspect of the script. ie that a TABLENAME_now().qvd will exist

All I need to do is check that something in the format TABLENAME*.qvd exists ready to be loaded.

Thanks

Mark

Re: How to check if a QVD file exists when it's name isn't fixed?

swuehl — Fri, 19 Jun 2015 16:58:40 GMT

let vFileExists = 0;

For each File in ('$(vDataPath)$(vTableName)_*.qvd')

let vFileExists = 1;

exit for when $(vFileExists) = 1;

NEXT File

You are missing the filelist() here:

For each File in FileList('$(vDataPath)$(vTableName)_*.qvd')

Re: How to check if a QVD file exists when it's name isn't fixed?

Fri, 19 Jun 2015 17:06:10 GMT

OK, it appears I had missed the filelist mask within the For loop definition....

So it works using:

For each File in filelist ('$(vDataPath)$(vTableName)_*.qvd')

Thanks

Mark

Re: How to check if a QVD file exists when it's name isn't fixed?

deborahs — Wed, 12 Jun 2019 18:59:23 GMT

Hello sir, I am an intern working on a project where I'm facing a similar issue. My objective is to check whether the file was created before 6.15 am of the current date and notify (trigger an email on nprint) the concerned person if it hasn't. I'm trying to use something similar and I'm inserting a where clause but i still can't figure it out! Any help would be appreciated! Thanks in advance!!! 🙂