File Script Functions

File script functions return information about the file currently being read by Qlik Sense as part of the script execution. They are particularly useful for monitoring apps or for controlling an app’s script execution (e.g. checking if the QVD has been updated before proceeding to reload).

This article will cover the basic principles of working with file functions in the data load editor, including:

  • The general function syntax
  • Working with file functions in the data load editor

For a complete up-to-date list of file functions, please see the Qlik Sense help pages here.

The file function syntax

The syntax will differ depending on the function. For up to date syntax, please do refer to the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

Unfortunately, it isn’t always easy to decipher the syntax provided in the help pages linked to above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages. Syntax for file functions is relatively easy to read.

To explain the syntax, we will use a couple of different functions that have a few different syntax elements and decipher them in turn.

Example 1: FileTime

This function returns the last modified date time of a specified file. The date time is given in UTC time zone.

Copy code 


FileTime([ filename ])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. The various elements you will need to enter are separated by commas. In this function, there is only one element: [filename].
  3. Anything between square brackets [] is an optional parameter. In this function, the only parameter is optional. This parameter allows you to specify a specific file to look at. If you leave it blank, Qlik will look at the file currently being read. If you do include the parameter, DO NOT include brackets.

An example of this function being used with the optional parameter included could be:

Copy code 


FileTime('lib://Public:DataFiles/my_data.xlsx')

The above function will return the date and time that file my_data was last modified.

An example of the function without using optional parameters may be:

Copy code 


FileTime()

The above function will now return the date and time that file currently being loaded was last modified.

Example 2: QvdNoOfRecords

This function returns the number of records in a given QVD file.

Copy code 


QvdNoOfRecords(filename)

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ()
  2. The various elements you will need to enter are separated by commas. In this function, there is only one element: filename.
  3. Anything between square brackets [] is an optional parameter. In this function, there are no optional parameters.

An example of this function being used could be:

Copy code 


QvdNoOfRecords('lib://Public:DataFiles/my_data.qvd')

The above function will return the number of rows in the file my_data.qvd.

How to use file functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use these functions in the load. The rules are:

  • Some of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • They are particularly useful for controlling the flow of the execution
  • The functions can be nested and combined with other types of functions

A simple script execution control to check for up to date data may look something like this:

Copy code 


Let vToday = Today();
Let vFile = Date(Floor(FileTime('lib://Public:DataFiles/my_data.qvd')));

Do Until $(vToday) = $(vFile)

	Sleep 10000; //10 seconds
    Let vFile = Date(Floor(FileTime('lib://Public:DataFiles/my_data.qvd')));

Loop;

LOAD
	*
FROM [lib://Public:DataFiles/my_data.qvd] (qvd);

When the modified date of file my_data.qvd is before today, the script will halt for 10 seconds and then check the date again. When the date finally matches today, the data from my_data.qvd will be loaded and the execution will stop.