Qlik Sense Table Functions With Syntax

1. Objective

In our last tutorial, we discussed Qlik Sense Trigonometric and Hyperbolic functions. Today, we will see Qlik Sense Table Functions. Moreover, the table functions are used to apply certain operations on the tables which the current LOAD statement is reading or evaluating. These functions make managing fields and data values in a table easy and convenient especially when a lot of data is being dealt with, in a single data load script.

So, let’s start Qlik Sense Table Functions.

Qlik Sense Table Functions With Syntax

2. Qlik Sense Table Functions

i. FieldName() function

This function is used when you want to return the name of a field from a previously loaded table. You will have to specify the name of the table if you are using this function outside of the LOAD statement in which that particular table is been loaded. But if you are using this function within the piece of code which has the concerned table within the LOAD statement, you must not specify the table’s name as it would be automatically understood by the system’s logic.

Have a look at Qlik Sense Logical Functions

The syntax of FieldName function:

FieldName(field_number ,table_name)

Where field_number is the number of the field in the table.

And, table_name is the name of the table from which you want the function to find the field name through the field number.

For instance, the piece of code given below uses the FieldName function to call the 2nd field i.e. Attribute field in the script statement. This is an example of using this function within the same LOAD statement as the table.

LOAD *
FieldName(2)
Inline [
AttributeCode, Attribute
R, Red
Y, Yellow
B, Blue
C, Cotton
P, Polyester
S, Small
M, Medium
L, Large
];

If you want to use this function out of the same LOAD statement then it would be as given below. Which assigns the function as a variable x and calls the fieldname from tab1 at the fourth position.

Let’s revise Qlik Sense Mathematical Functions

LET x = FieldName(4,'tab1');

ii. FieldNumber() function

The FieldNumber() function returns the number of the field in a table. This function can either be used within the LOAD script where the table is loaded or outside of it by calling the field and table in which the field resides.

The syntax of Qlik Sense FieldNumber Function:

FieldNumber(field_name ,table_name)

Where field_name is the name of the field of which you want to know the number. And, table_name is the name of the table which contains the concerned field.

For example,

LET x = FieldNumber('Customer','Sales');

Suppose the field name ‘Customer’ mentioned in the sample code above is the 2nd field in the table ‘Sales’. Then this function would return 2. Note that we have mentioned the name of the table because we are using it out of the LOAD statement loading the table ‘Sales’. Although if you use this function within the LOAD statement for Sales table then mentioning the name in the function is not required. If no such field as you mentioned in the function is available in the table then 0 is returned.

Do you know about Qlik Sense Mapping Functions

iii. NoOfFields() function

This function is used to return the total number of fields present in a table.

The syntax of Qlik Sense NoOfFields function:

NoOfFields(table_name)

Where in place of table_name parameter, you must enter the name of the table of which you want to know the number of fields. However, if you are using this function within the LOAD statement in which the concerned table is been loaded then the name of the table is not required as the function’s parameter.

For example, if in a table called Sales, there are a total of 8 fields then we will use the NoOfFields() function as,

LET x = NoOfFields(‘Sales’);

Returns 8 as the value for the variable x.

iv. NoOfRows() function

This function is used to return the total number of rows in a table.

The syntax of NoOfRows function in Qlik Sense:

NoOfRows(table_name)

Where table_name is the name of the table of which we want to know the number of rows.

For example, if there is a table named ‘Sales’ and there are 80 rows in it. Then using this function, you can get this value is a result.

Do you know about Qlik Sense Line Chart

LET x= NoOfRows(‘Sales’);

Returns 80 as the value equal to x.

v. NoOfTables() function

This function returns the total number of tables that are loaded in a LOAD statement.

The syntax of NoOfTables Function:

NoOfTables()

Suppose you have loaded 10 different tables within a LOAD statement, then this function will return 10.

vi. TableName() function

This function returns the name of the table corresponding to the unique number that the table has.

The syntax of TableName Function in Qlik Sense:

TableName(table_number)

Where table_number is the n of the nth number of tables that has been loaded into a particular LOAD statement.

vii. TableNumber() function

This function returns the number assigned to a specific table in a LOAD statement as a part of the script. In every LOAD statement, the tables are numbered, and the first table is numbered 0 and the second table 1 and so on.

Let’s revise Qlik Sense Conditional Functions

The syntax of TableNumber Function:

TableNumber(table_name)

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

3. Conclusion

Hence, we can use these Qlik Sense Table Functions in the data load script of Qlik Sense. Moreover, only NoOfRows function we can use as a chart function. Table functions are very useful when you want to manage all the tables loaded in your script by having all the information like total number of tables, table names, table numbers, number of rows etc.

Still, if you have any query regarding Qlik Sense Table Functions, ask in the comment tab.

See also – 

Qlik Sense Treemap Visualizations

Reference for Qlik Sense