{"id":43323,"date":"2018-11-27T11:47:18","date_gmt":"2018-11-27T06:17:18","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=43323"},"modified":"2018-11-24T17:47:31","modified_gmt":"2018-11-24T12:17:31","slug":"inter-record-functions-in-qlik-sense","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/inter-record-functions-in-qlik-sense\/","title":{"rendered":"Inter Record Functions in Qlik Sense With Syntax & Example"},"content":{"rendered":"

1. Objective<\/span><\/h2>\n

Earlier we have discussed, Qlik Sense Statistical Distribution Function<\/strong>. Today, we will see Qlik Sense<\/strong><\/a> 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.<\/span><\/p>\n

\"Qlik<\/a>

Qlik Sense Inter Record Functions<\/p><\/div>\n

So, let’s start Qlik Sense\u00a0 Inter Record Functions Tutorial.<\/p>\n

2. Qlik Sense Inter Record Functions<\/h2>\n

i. Row Functions<\/span><\/h3>\n

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 –<\/p>\n

\"Qlik<\/a>

Qlik Sense Inter Record Functions – Row Functions<\/p><\/div>\n

a. Above<\/span><\/h4>\n

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.<\/span><\/p>\n

You must read –<\/strong> Qlik Sense Field Functions<\/a><\/strong><\/p>\n

Syntax:<\/strong><\/p>\n

Above([TOTAL] expr [ , offset [,count]])<\/pre>\n
Where, <\/span>expr<\/span> is the dimension or field name from which we want to fetch the record values. <\/span><\/p>\n
Offset<\/span> 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).<\/span><\/p>\n
Count<\/span> parameters is used to return the count of values encountered in each row while going up to the desired row. <\/span><\/p>\n
TOTAL<\/span> 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. <\/span><\/p>\n
For example<\/strong>, we have sales values of a group of 4 customers. Using the <\/span>Above()<\/b> 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. <\/span><\/p>\n\n\n\n\n\n\n\n
Customer<\/b><\/td>\nSales<\/b><\/td>\nAbove(Sales)<\/b><\/td>\nSales-Above(Sales)<\/b><\/td>\n<\/tr>\n
Rebecca<\/span><\/td>\n567<\/span><\/td>\n–<\/span><\/td>\n–<\/span><\/td>\n<\/tr>\n
Ethan<\/span><\/td>\n680<\/span><\/td>\n567<\/span><\/td>\n113<\/span><\/td>\n<\/tr>\n
Joshua<\/span><\/td>\n764<\/span><\/td>\n680<\/span><\/td>\n84<\/span><\/td>\n<\/tr>\n
Mathew<\/span><\/td>\n860<\/span><\/td>\n764<\/span><\/td>\n96<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
b. Below<\/span><\/h4>\n
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. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
Below([TOTAL] expr [ , offset [,count]])<\/pre>\n
Where, <\/span>expr<\/span> is the dimension or field name from which we want to fetch the record values. <\/span><\/p>\n
Offset<\/span> 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).<\/span><\/p>\n
Recommended Reading –<\/strong> Qlik Sense String Functions<\/a><\/strong><\/p>\n
Count<\/span> parameters are used to return the count of values encountered in each row while going up to the desired row. <\/span><\/p>\n
TOTAL<\/span> 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. <\/span><\/p>\n
For example, we have the sales values of a group of 4 customers. Using the <\/span>Below()<\/b> 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. <\/span><\/p>\n\n\n\n\n\n\n\n
Customer<\/b><\/td>\nSales<\/b><\/td>\nBelow(Sales)<\/b><\/td>\nSales+Below(Sales)<\/b><\/td>\n<\/tr>\n
Rebecca<\/span><\/td>\n567<\/span><\/td>\n680<\/span><\/td>\n1247<\/span><\/td>\n<\/tr>\n
Ethan<\/span><\/td>\n680<\/span><\/td>\n746<\/span><\/td>\n1426<\/span><\/td>\n<\/tr>\n
Joshua<\/span><\/td>\n764<\/span><\/td>\n860<\/span><\/td>\n1624<\/span><\/td>\n<\/tr>\n
Mathew<\/span><\/td>\n860<\/span><\/td>\n–<\/span><\/td>\n–<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
c. Bottom<\/span><\/h4>\n
The bottom() function returns the bottommost value from the rows of a concerned column. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
Bottom([TOTAL] expr [ , offset [,count ]])<\/pre>\n
Where, <\/span>expr<\/span> is the dimension or field name from which we want to fetch the record values. <\/span><\/p>\n
Offset<\/span> 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 <\/span>Top()<\/b> function. <\/span><\/p>\n
Count<\/span> parameters is used to return the count of values encountered in each row while going up to the desired row. <\/span><\/p>\n
TOTAL<\/span> 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. <\/span><\/p>\n
For example, the Bottom(Sales) expression has returned the value 860 from the bottom-most row. <\/span><\/p>\n\n\n\n\n\n\n\n
Customer<\/b><\/td>\nSales<\/b><\/td>\nBottom(Sales)<\/b><\/td>\nSales+Below(Sales)<\/b><\/td>\n<\/tr>\n
Rebecca<\/span><\/td>\n567<\/span><\/td>\n860<\/span><\/td>\n1427<\/span><\/td>\n<\/tr>\n
Ethan<\/span><\/td>\n680<\/span><\/td>\n860<\/span><\/td>\n1540<\/span><\/td>\n<\/tr>\n
Joshua<\/span><\/td>\n764<\/span><\/td>\n860<\/span><\/td>\n1624<\/span><\/td>\n<\/tr>\n
Mathew<\/span><\/td>\n860<\/span><\/td>\n860<\/span><\/td>\n1720<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
What do you know about Qlik Sense Capabilities<\/a><\/strong><\/p>\n
d. Top<\/span><\/h4>\n
The top() function returns the value from the topmost row in a column. This function is just the opposite of Bottom() function.<\/span><\/p>\n
Syntax:<\/strong><\/p>\n
Top([TOTAL] expr [ , offset [,count ]])<\/pre>\n
Where, <\/span>expr<\/span> is the dimension or field name from which we want to fetch the record values. <\/span><\/p>\n
Offset<\/span> 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 <\/span>Bottom()<\/b> function. <\/span><\/p>\n
Count<\/span> parameters are used to return the count of values encountered in each row while going up to the desired row. <\/span><\/p>\n
TOTAL<\/span> 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. <\/span><\/p>\n
For example, the Top(Sales) expression has returned the value 567 from the topmost row. <\/span><\/p>\n\n\n\n\n\n\n\n
Customer<\/b><\/td>\nSales<\/b><\/td>\nTop(Sales)<\/b><\/td>\nSales+Top(Sales)<\/b><\/td>\n<\/tr>\n
Rebecca<\/span><\/td>\n567<\/span><\/td>\n567<\/span><\/td>\n1134<\/span><\/td>\n<\/tr>\n
Ethan<\/span><\/td>\n680<\/span><\/td>\n567<\/span><\/td>\n1247<\/span><\/td>\n<\/tr>\n
Joshua<\/span><\/td>\n764<\/span><\/td>\n567<\/span><\/td>\n1331<\/span><\/td>\n<\/tr>\n
Mathew<\/span><\/td>\n860<\/span><\/td>\n567<\/span><\/td>\n1427<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
e. NoOfRows<\/span><\/h4>\n
This function returns the total number of rows present in a particular column or field in a table. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
NoOfRows([TOTAL])<\/pre>\n
Where,<\/span> TOTAL<\/span> 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. <\/span><\/p>\n
For example, if a field named \u2018Customer\u2019 has 50 rows each having a new customer name, then the expression <\/span>NoOfRows()<\/span> will return 50 if the current field is Customers.<\/span><\/p>\n
We recommend you to read –<\/strong> Qlik Sense Data Model<\/a><\/strong><\/p>\n
ii. Column Functions<\/span><\/h3>\n
\"Qlik<\/a>
Qlik Sense Inter Record Functions – Column Functions<\/p><\/div>\n
a. Column<\/span><\/h4>\n
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. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
Column(ColumnNo)<\/pre>\n
Where <\/span>ColumnNo<\/span> is the number of measure column that you want to use. <\/span><\/p>\n
For example,<\/strong> <\/span><\/p>\n\n\n\n\n\n\n\n
Customer<\/b><\/td>\nYear<\/b><\/td>\nOrder Value<\/b><\/td>\nTotal Sales Value<\/b><\/td>\n%Sales<\/b><\/td>\n<\/tr>\n
Rebecca<\/span><\/td>\n2017<\/span><\/td>\n150<\/span><\/td>\n505<\/span><\/td>\n29.70<\/span><\/td>\n<\/tr>\n
Ethan<\/span><\/td>\n2017<\/span><\/td>\n64<\/span><\/td>\n505<\/span><\/td>\n12.67<\/span><\/td>\n<\/tr>\n
Joshua<\/span><\/td>\n2017<\/span><\/td>\n81<\/span><\/td>\n505<\/span><\/td>\n16.04<\/span><\/td>\n<\/tr>\n
Mathew<\/span><\/td>\n2017<\/span><\/td>\n50<\/span><\/td>\n505<\/span><\/td>\n9.90<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
b. Dimensionality<\/span><\/h4>\n
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. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
Dimensionality( )<\/pre>\n
For example<\/strong>, if a row has three segments from three columns which are dimensions such as, \u2018<\/span>Customer\u2019<\/span><\/i>, \u2018<\/span>Product\u2019<\/span><\/i> and \u2018<\/span>City\u2019<\/span><\/i> then this function will return 3. <\/span><\/p>\n
c. Secondarydimensionality<\/span><\/h4>\n
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.<\/span><\/p>\n
You must read –<\/strong> Qlik Sense Gauge Chart<\/a><\/strong><\/p>\n
Syntax:<\/strong><\/p>\n
SecondaryDimensionality( )<\/pre>\n
Always an integer value is returned. <\/span><\/p>\n
iii. Field Functions\u00a0<\/span><\/h3>\n
\"Qlik<\/a>
Qlik Sense Inter Record Functions – Field Functions<\/p><\/div>\n
a. FieldIndex<\/span><\/h4>\n
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. <\/span><\/p>\n
Syntax:<\/strong><\/p>\n
FieldIndex(field_name , value)<\/pre>\n
Where, <\/span>field_name<\/span> is the name of the field in a particular table.<\/span><\/p>\n
value<\/span> is the field value whose position in the field or column you want to get as an index value. <\/span><\/p>\n
For example<\/strong>, in the script given below, there are certain fields loaded having a series of values.<\/span><\/p>\n
Names:\r\nLOAD * inline [\r\n\"FirstName\"|\"LastName\"|\u201dInitials\u201d|\"HasCellphone\"\r\nJay|Verma|JV|Yes\r\nSushmita|Bisht|SB|Yes\r\nMrinal|Thakur|MT |No\r\nPraveen|Deshpande|PD|No\r\nJiva|Singh|JS|Yes\r\nPurvi|Fadnavis|PF|Yes ] (delimiter is '|');<\/pre>\n
We can obtain the index values for specific field values. For instance, the expression, <\/span><\/p>\n