Qlik Sense File Functions With Syntax and Example

1. Objective

Earlier, we have discussed Qlik Sense Ranking Functions. Today, we will see Qlik Sense File Functions. Moreover, we will see the use of file functions in Qlik Sense to manage table files that are loaded in the data load scripts. Thus, these functions are not applicable on any other type of data sources used and return NULL if used.

Qlik Sense File Functions With Syntax and Example

Qlik Sense File Functions With Syntax and Example

So, let’s start Qlik Sense File Functions Tutorial.

2. Qlik Sense File Functions

Following are the Qlik Sense File Functions, let’s discuss them in detail –

You must check – Qlik Sense Integer Functions

i. Attribute() Function

This function returns the attribute names also known as the meta tags of a file. The meta tags must be saved as a part of the file itself for the function to read them. Qlik Sense supports a number of file formats such as MP3, WMA, WMV, PNG, and JPG.

Syntax for Qlik Sense File Functions:

Attribute(filename, attributename)

Where filename is the name of the file whose attributes or meta tag you wish to get. In some cases, you might also need to specify the path of the file, like, ‘lib://Table Files/’.

attributename is the name of the attributes or meta tags which you want this function to return.

For example,

Attribute(FileLongName, ‘ImageWidth’) as ImageWidth,

Attribute(FileLongName, ‘ImageLength’) as ImageLength,

Attribute(FileLongName, ‘BitsPerSample’) as BitsPerSample,

Attribute(FileLongName, ‘Compression’) as Compression,

ii. ConnectString() function

This function returns a string having the names of the active or currently connected ODBC or OLE DB connections. If no active connection exists, then the function will return an empty string.

Syntax for Qlik Sense File Functions:

ConnectString()

For example, if the script is connected to an OBDC connection named Sales then the function will return the same name in a string.

Have a look at Qlik Sense in Function

Load ConnectString() as ODBCConnection will return the string ‘Sales’ in the field ODBCConnection.

iii. FileBaseName() Function

This function returns a string containing the base name of the table file currently being read in the script. The base name is only the name of the file without the rest of the file path details.

Syntax for Qlik Sense File Functions:

FileBaseName()

For example, if the file with the path C:\UserFiles\Sales.txt is read by the system currently. Then the function FileBaseName() will return the string ‘Sales’.

iv. FileDir() function

This function returns the path to the directory of the table file currently being read in the script as a string.

Syntax for Qlik Sense File Functions:

FileDir()

For example, if a table file has the path C:\UserFiles\Sales.txt then,

FileDir() will return ‘C:\UserFiles’ as the directory for the file Sales.

v. FileExtension() function

This function returns the extension of the table file as string. The table file must be one currently being read.

Syntax for Qlik Sense File Functions:

FileExtension()

For example, if a table file has the path C:\UserFiles\Sales.txt then,

FileExtension() will return ‘txt’ as the extension of the file Sales.

vi. FileName() function

This function returns a string containing the file name with it’s extension.

Recommended Reading – Qlik Sense Table Functions

Syntax for Qlik Sense File Functions:

FileName()

For example, if a table file has the path C:\UserFiles\Sales.txt then,

FileName() will return ‘Sales.txt’ as the name of the file Sales.

vii. FilePath() Function

This function returns the entire path to a table file currently being read in the script. The data type is string.

Syntax for Qlik Sense File Functions:

FilePath()

For example, if a table file has the path C:\UserFiles\Sales.txt then,

FilePath() will return ‘C:\UserFiles\Sales.txt’ as the path of the file.

viii. FileSize() function

This function returns the size of the file in bytes, as an integer value. The filename can be specified and if not, the size of the table file currently being loaded is returned.

Syntax for Qlik Sense File Functions:

FileSize([filename])

For example, if a table file has the path C:\UserFiles\Sales.txt then,

FileSize(‘C:\UserFiles\Sales.txt’) will return 65,724 as the size of the file Sales.txt in bytes.

ix. FileTime() Function

This function returns a string containing the timestamp i.e. date and time of the last time a file or table file was modified. You can also specify the filename for which you wish to obtain a timestamp.

We recommend you to read Qlik Sense Logical functions

Syntax for Qlik Sense File Functions:

FileTime([ filename ])

For example, the file ‘Sales.xls’ was modified at 2018-11-15 1:28:00 then the function FileTime(‘Sales.xls’) will return ‘2018-11-15 1:28:00’ as the timestamp.

x. GetFolderPath() function

This function returns a string having the full path of a Microsoft Windows folder.

Syntax for Qlik Sense File Functions:

GetFolderPath(foldername)

For instance, the sample script given below calls for some folder paths.

LOAD
GetFolderPath('Music')
GetFolderPath('Pictures')
GetFolderPath('Windows')
AutoGenerate 1;

This will return the paths for each folder mentioned in the function,

C:\Users\smu\Music for the folder Music.

C:\Users\smu\Pictures for the folder Pictures.

C:\Windows for the folder Windows.

xi. QvdCreateTime() function

This function returns a string having the XML-header time stamp of a QVD file. It only returns the timestamp if there exists a QVD file in the script. Otherwise, it returns NULL.

Syntax for Qlik Sense File Functions:

QvdCreateTime(filename)

For example,

QvdCreateTime(‘Sales.qvd’)will return a XML timestamp like “2018-02-20 12:29”

QvdCreateTime(‘C:\MyDir\Sales.qvd’)

xii. QvdFieldName() function

This function returns the name of the field present in a QVD file. This function takes in the field number and file name of the QVD file and returns the name of the field corresponding to that field number. If no field corresponding to the field number is present, then the function returns NULL.

Let’s revise Qlik Sense Null Functions

Syntax for Qlik Sense File Functions:

QvdFieldName(filename , fieldno)

Where, fieldname is the name of the QVD file in which we want to look for a field.

fieldno is the integer specifying the number of fields, whose name you want to get from this function.

For instance,

In a QVD file named Sales, the third field is ProductName, then the function will be used as,

QvdFieldName(‘Sales.qvd’,3) will return a string ‘ProductName’.

xiii. QvdNoOfFields() function

This function returns the total number of fields in a QVD file.

Syntax for Qlik Sense File Functions:

QvdNoOfFields(filename)

For example,

QvdNoOfFields(‘Sales.qvd’) returns 5 if the file Sales.qvd has five fields in total.

xiv. QvdNoOfRecords() function

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

Syntax for Qlik Sense File Functions:

QvdNoOfRecords(filename)

For example,

QvdNoOfRecords(‘Sales.qvd’) returns 50 if the file Sales.qvd has fifty fields in total.

xv. QvdTableName() function

This function returns the name of the table in the QVD file loaded.

Do you know about Qlik Sense Financial Functions

Syntax for Qlik Sense File Functions:

QvdTableName(filename)

For example, if a QVD file named Sales.qvd contains the table ‘Year2018’ then the function QvdTableName(Sales.qvd) will return the string ‘Year2018’.

So, this was all in Qlik Sense File Functions. Hope you like our explanation.

3. Conclusion

These were all the important file functions that we use in data load scripts of Qlik Sense to manage and deal with files, especially table files being loaded into Qlik Sense’s memory. Only ConnectString() function is applicable to files other than table files i.e. ODBC and OLE DB files.

Still, if you are having any confusion in Qlik Sense File Functions, ask in the comment tab.

See also – 

Qlik Sense Visualizations

Reference for Qlik Sense

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.