Inter Record Functions in Qlik Sense With Syntax & Example

FREE Online Courses: Dive into Knowledge for Free. Learn More!

1. Objective

Earlier we have discussed, Qlik Sense Statistical Distribution Function. Today, we will see Qlik Sense Inter Record Functions. We use the inter record functions when values are required inter-records i.e. values of different or previously loaded records. When we refer to inter records in data load script, then the records that have been previously loaded are being referred. And in case of inter records in charts, then another value from a different data set is being referred.

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions

So, let’s start Qlik Sense  Inter Record Functions Tutorial.

2. Qlik Sense Inter Record Functions

i. Row Functions

So, there are 5 functions in row functions of Qlik Sense Inter Record Functions they are Above, Below, Bottom, Top, NoOfRows. Let’s discuss these functions in detail –

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions – Row Functions

a. Above

This function returns a value which is exactly above the current row and in the same column or field segment. You can set the number of rows above the current row from which you want to fetch the value using the offset value.

You must read – Qlik Sense Field Functions

Syntax:

Above([TOTAL] expr [ , offset [,count]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter which sets the number of rows the functions should go up from the current row. The default is set to 1 which fetches the value from one row just above the current row. If you specify the offset value as a negative value, then the values are fetched from rows below the current row (works like Below function).

Count parameters is used to return the count of values encountered in each row while going up to the desired row.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, we have sales values of a group of 4 customers. Using the Above() function, we will create a new field where values from one row up will be fetched and in another column, the two values will be subtracted showing the difference of sales between two customers.

CustomerSalesAbove(Sales)Sales-Above(Sales)
Rebecca567
Ethan680567113
Joshua76468084
Mathew86076496

b. Below

This function returns a value which is exactly below the current row and in the same column or field segment. You can set the number of row below the current row from which you want to fetch the value using the offset value.

Syntax:

Below([TOTAL] expr [ , offset [,count]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter which sets the number of rows the functions should go down from the current row. The default is set to 1 which fetches the value from one row just below the current row. If you specify the offset value as a negative value, then the values are fetched from rows above the current row (works like Above function).

Recommended Reading – Qlik Sense String Functions

Count parameters are used to return the count of values encountered in each row while going up to the desired row.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, we have the sales values of a group of 4 customers. Using the Below() function, we will create a new field where values from one row down will be fetched and in another column, the two values will be added showing the total sales of the two customers.

CustomerSalesBelow(Sales)Sales+Below(Sales)
Rebecca5676801247
Ethan6807461426
Joshua7648601624
Mathew860

c. Bottom

The bottom() function returns the bottommost value from the rows of a concerned column.

Syntax:

Bottom([TOTAL] expr [ , offset [,count ]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows above the bottom row value. If this value is negative then this function works like the Top() function.

Count parameters is used to return the count of values encountered in each row while going up to the desired row.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, the Bottom(Sales) expression has returned the value 860 from the bottom-most row.

CustomerSalesBottom(Sales)Sales+Below(Sales)
Rebecca5678601427
Ethan6808601540
Joshua7648601624
Mathew8608601720

What do you know about Qlik Sense Capabilities

d. Top

The top() function returns the value from the topmost row in a column. This function is just the opposite of Bottom() function.

Syntax:

Top([TOTAL] expr [ , offset [,count ]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows below the top row value. If this value is negative then this function works like the Bottom() function.

Count parameters are used to return the count of values encountered in each row while going up to the desired row.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, the Top(Sales) expression has returned the value 567 from the topmost row.

CustomerSalesTop(Sales)Sales+Top(Sales)
Rebecca5675671134
Ethan6805671247
Joshua7645671331
Mathew8605671427

e. NoOfRows

This function returns the total number of rows present in a particular column or field in a table.

Syntax:

NoOfRows([TOTAL])

Where, TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, if a field named ‘Customer’ has 50 rows each having a new customer name, then the expression NoOfRows() will return 50 if the current field is Customers.

We recommend you to read – Qlik Sense Data Model

ii. Column Functions

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions – Column Functions

a. Column

The Column() function returns the values contained in a column using the column number. Only the measure columns or fields are considered by this function and dimensions are completely disregarded.

Syntax:

Column(ColumnNo)

Where ColumnNo is the number of measure column that you want to use.

For example,

CustomerYearOrder ValueTotal Sales Value%Sales
Rebecca201715050529.70
Ethan20176450512.67
Joshua20178150516.04
Mathew2017505059.90

b. Dimensionality

This function returns the dimensionality of a row i.e. the total number of dimensions that are present in a row. You can carry out several operations using this function as it is a convenient way to calculate the number of columns which are dimensions. This function can only be used in charts.

Syntax:

Dimensionality( )

For example, if a row has three segments from three columns which are dimensions such as, ‘Customer’, ‘Product’ and ‘City’ then this function will return 3.

c. Secondarydimensionality

This function is used to evaluate the dimensionality of a row in the horizontal pivot table. It works similar to the dimensionality() function and does not include aggregation values. This function returns 0 if it is used anywhere except in Pivot tables in Qlik Sense.

You must read – Qlik Sense Gauge Chart

Syntax:

SecondaryDimensionality( )

Always an integer value is returned.

iii. Field Functions 

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions – Field Functions

a. FieldIndex

The FieldIndex() function returns the index value or position of a value in a given field. This is known as indexing of field values. This function returns an integer value.

Syntax:

FieldIndex(field_name , value)

Where, field_name is the name of the field in a particular table.

value is the field value whose position in the field or column you want to get as an index value.

For example, in the script given below, there are certain fields loaded having a series of values.

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

We can obtain the index values for specific field values. For instance, the expression,

  • FieldIndex(‘FirstName’,’Jay’) returns 1 because Jay is the first value in the field FirstName.
  • FieldIndex(‘LastName’,’Deshpande’) returns 4.
  • Position1:

Load FieldIndex(‘FirstName’,’Purvi’) as Position

Resident Names;

Returns, Position=6.

  • Position2:

Load FieldIndex(‘FirstName’,’Mrinal’) as MrinalPosition

Resident Names;

Returns, MrinalPosition=3.

b. FieldValue

This function is the inverse of the FieldIndex() function. This function returns the value in a field from the given index number or position of the value in the field counted from top to bottom.

Syntax:

FieldValue(field_name , elem_no)

Where, field_name is the name of the field in a particular table.

Element_no is the position of the value that you want to fetch counted in the load order in a field.  

For example, in the script given below, there are certain fields loaded having a series of values.

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

We can obtain the values corresponding to particular index position values. For instance, the expression,

  • FieldValue(‘FirstName’,’1′) returns Jay, because Jay is the first value in the field FirstName.
  • FieldValue(‘LastName’,’4′) returns ‘Deshpande’.
  • Position1:

Load FieldValue(‘FirstName’,’6′) as Position

Have a look at Box Plot in Qlik Sense Visualization

Resident Names;

Returns, Position=Purvi.

  • Position2:

Load FieldValue(‘FirstName’,’3′) as MPosition

Resident Names;

Returns, MPosition=Mrinal.

c. FieldValueCount

This function returns the count or total number of values in a specific field.

Syntax:

FieldValueCount(field_name)

Where, field_name is the name of the field in a particular table.

For example, in the code given below, we can use this function to count the total values in one of the fields.

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

We can use this function to calculate the total values in any of the fields uploaded in the table, such as, ‘FirstName’, ‘LastName’, ‘Initials’, ‘HasCellphone’.

FieldValueCount(‘FirstName’) returns 6 as there are six values in the field FirstName.

Similarly, the function FirstValueCount(‘LastName’) returns 6.

iv. Pivot Table Functions

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions – Pivot Table Functions

a. After

This function evaluates the values in a row horizontally and returns the value present after or to the right of a currently selected cell.

Syntax:

after([TOTAL] expr [, offset [, count ]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows further the current row. If this value is negative then this function works like the Before() function.

Recommended Reading – Qlik Sense Treemap Visualization

Count parameters are used to return the count of values encountered while moving n rows further as specified by the count parameter.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example,

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

When you use this function like,

After(FirstName) returns the values from the column ‘LastName’ as it lays right to the FirstName column.

b. Before

This function evaluates the values in a row horizontally and returns the value present before or to the left of a currently selected cell.

Syntax:

Before([TOTAL] expr [, offset [, count ]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows behind or to the left of the current row. If this value is negative then this function works like the After() function.

Count parameters are used to return the count of values encountered while moving n rows backward as specified by the count parameter.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example,

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

When you use this function like,

Before(LastName) returns the values from the column ‘FirstName’ as it lays before or left to the LastName column.

c. First

This function returns the first value in the column of the current row. This function can only be used in a pivot table.

Syntax:

first([TOTAL] expr [, offset [, count]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows further or to the right of the current cell. If this value is negative then this function works like the Last() function.

Count parameters is used to return the count of values encountered while moving n rows further as specified by the count parameter.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example, the expression, first(FirstName) will return Jay as it is the first value of the first row in the table. (refer to the sample data loaded in the functions above).

d. Last

This function returns the last value in the column of the current row. This function can only be used in a pivot table.

Let’s discuss more in Qlik Sense Table Functions

Syntax:

last([TOTAL] expr [, offset [, count]])

Where, expr is the dimension or field name from which we want to fetch the record values.

Offset is the parameter using which you can fetch value, n rows further or to the left of the current cell. If this value is negative then this function works like the First() function.

Count parameters is used to return the count of values encountered while moving n rows further towards the left as specified by the count parameter.

TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

For example,

Names:
LOAD * inline [
"FirstName"|"LastName"|”Initials”|"HasCellphone"
Jay|Verma|JV|Yes
Sushmita|Bisht|SB|Yes
Mrinal|Thakur|MT |No
Praveen|Deshpande|PD|No
Jiva|Singh|JS|Yes
Purvi|Fadnavis|PF|Yes ] (delimiter is '|');

The expression, last(FirstName,0,4) will return four values from the last row i.e. Purvi, Fadnavis, PF, Yes.

e. ColumnNo

This function returns the total number of columns present in a particular row (upon evaluating horizontally).

Syntax:

ColumnNo([total])

Where, TOTAL is a qualifier when used (in the one-dimensional table) returns the current value to be equal to all the values in the entire column.

The columns in a row are counted from 1 for the first row and so on for the column segments to follow.

For example, if we want the column number of a row, we use this function as ColumnNo() then you will get 4 as the column numbers for the table ‘Names’ (given in functions above).

v. Data Load Script Functions

Qlik Sense Inter Record Functions

Qlik Sense Inter Record Functions – Data Load Script Functioons

a. Exists

This function checks whether a value exists in a field or in other words, has been loaded into the script or not. If yes, then it returns -1 or True and if no, then this function returns 0 or False as the Boolean values. Such results can be further used in Where or If statements in the script.

Syntax:

Exists(field_name [, expr])

Where, field_name is the name of the field or column you want to look up the value in.

expr is the value which you want to check exists or not.

For instance, the expression exists(FirstName, ‘Gaurav’) will return -1, True if the data load script contains Gaurav in the records or else 0, False will be returned by this function.

What is the use of Trigonometric and Hyperbolic Functions in Qlik Sense

b. LookUp

This function searches or looks up for a value present in a specific field in a currently or previously loaded script.

Syntax:

lookup(field_name, match_field_name, match_field_value [, table_name])

Where, field_name is the name of the field for which you wish to lookup for certain values. You must write this in single-quoted string literals.

Match_field_name is the name of the field whose value you want to find and match with the other field. This parameter should also be enclosed as quotes literals.

Match_field_value is the specific field value you are looking for.

Table_name is the name of the table you want to search the value in.

For example, the expression, Lookup(‘Category’, ‘ProductID’, ProductID, ‘ProductList’) as CategoryID will fist evaluate the values from the field ‘Category’ then match it with the values of ‘ProductID’ from the table ProductList and return the results in the field CategoryID.

c. Peek

This function finds and returns a value of a field from a specific row. The row number and table name can be specified in the function itself. The tables that this function searches in for values are either currently loaded in the script or the values already exist in the internal storage.

Syntax:

Peek(field_name[, row_no[, table_name ] ])

Where, field_name is the name of the field for which you want to find values in a row of a field. You must write this in single-quoted string literals.

Row_no is the specific number of row that you want such as, 0 for the first record, 1 for the second and so on. If you use a negative number, then the values will be evaluated from bottom to top direction.

Table_name is the name of the table consisting of the concerned fields.

d. Previous

This function returns the data values from previous records, probably residing in a different table.

Syntax:

Previous(expr)

Where expr is the previous field from which you want to fetch values in a current table.

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

3. Conclusion

Hence, in this Inter Record Functions in Qlik Sense Tutorial, we discussed all the crucial inter record functions which are primarily used in a table to deal with values, fields, rows etc. Also, we saw row functions, column functions, field functions, pivot table functions, data load script functions. Moreover, we discussed how this inter record functions work in Qlik Sense with the help of syntax and example. 

See also – 

Qlik Sense Career Opportunities 

Reference for Qlik Sense 

Your opinion matters
Please write your valuable feedback about DataFlair on Google

follow dataflair on YouTube

Leave a Reply

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