Checking if a File Exists

A question that comes up fairly regularly from customers and also on the QlikCommunity is how do you check if a file exists before loading it? As with everything in QV there is more than one way to do this. Below I'll demonstrate the 3 methods most commonly used in QV although there are without a doubt more. 

Personally I would use method 1 if dealing with a single QVD file and method 2 for all other files or when dealing with wild cards in file names. I would avoid method 3; although it is a completely valid method, in my opinion, it belongs within a much more complex custom error handling routine which is outside the scope of this post.

Method 1 - qvdCreatTime()

This method uses an inbuilt function within QV called qvdCreatTime() which returns the date and time the QVD was created. If the QVD doesn't exist, qvdCreatTime() returns NULL and thus we can use it to check if a QVD exists. Obviously this method is limited to QVD files only. It also cannot handle wild cards.

IF (NOT isNull(qvdCreateTime('myqvdfile.qvd'))) THEN
    LOAD * FROM myqvdfile.qvd (qvd);
END IF

Method 2 - For Each File in Filelist()

This method uses the filelist functions within a for loop. This function is only possible within the loop and has the benefit that wild cards can be used within the file name and path. This allows you to load multiple files that match a pattern by looping through them. Assuming the field names are all identical the files will be concatenated into a single table. Unlike method 1 above, this method can be used on any file type.

FOR each File in filelist ('myqvdfile.qvd')
     LOAD * FROM myqvdfile.qvd (qvd);
NEXT File 

Method 3 - ErrorMode & ScriptError

This method isn't strictly checking if a file exists but rather changing the way QlikView reacts to errors if it tries to load a file that it can't find. To do this we change the value of the ErrorMode variable to 0 which prevents QV from stopping the script execution if an error occurs. We then try to load the table and afterwards check the variable ScriptError to see if an error has occurred. If ScriptError has a value of 1 then no error occurred. A value of 8 denotes that the error that occurred was that the file does not exist. We can replace the TRACE statements with the relevant actions to take. We then set ErrorMode back to it's default value of 1 and continue the rest of the script.

SET ErrorMode = 0;

LOAD * FROM myqvdfile.qvd (qvd);

IF ScriptError>1 AND ScriptError<>8 THEN
    TRACE File not found;
ELSE
    TRACE File loaded;
END IF

SET ErrorMode = 1;