Qlik Sense Counter Functions With Syntax

1. Objective – Counter Functions in Qlik Sense

In our last Qlik Sense Tutorial, we discussed Qlik Sense Pie Chart. Today, we will Qlik Sense Counter Functions. We call them counter functions because they apply to records when encountered during the load script execution of the LOAD statement. Moreover, these are all script functions that we use in the load script code. Also, we will see the syntax for Qlik Sense Counter Functions. 

So, let’s start the Qlik Sense Counter Functions Tutorial.

Qlik Sense Counter Functions With Syntax

You must read about Qlik Sense Color Functions

2. Qlik Sense Counter Functions

Following are some Counter Functions in Qlik Sense –

i. autonumber() in Qlik Sense

The autonumber() function assigns an integer value to every unique record encountered during script execution. This is done to store large data records into compact composite keys where integer values are assigned to distinct record values as per the expression specified in autonumber function.

The syntax for Qlik Sense aurtonumber function – 

autonumber(expression[ , AutoID])

where expression is the record names that you want to make composite keys for using autonumber.

Basically, the AutoID is an optional parameter or an ID that you can assign to individual autonumber counters in case you are creating more than one in the same script.

For example, we have created a composite key for the records of combination of the fields Region, Year and Month.

RegionSales in Qlik Sense autonumber:

LOAD *,
AutoNumber(Region&Year&Month) as RYMkey;
LOAD * INLINE
[ Region, Year, Month, Sales
North, 2017, May, 245
North, 2017, May, 347
North, 2017, June, 127
South, 2017, June, 645
South, 2018, May, 367
South, 2018, May, 221
];

So, this composite key will assign integers to the records of the table we loaded inline.

Have a look at Qlik Sense Histogram Visualization

As you can see in the table, for each new combination of region, year and month, there is a unique RYMkey. Such RYMkeys are the composite keys made using autonumber() function. So, we use these keys further in the script when values corresponding to the keys use in a calculation. For example, using the RYMkey values, the sales values corresponding to each RYM combination can be used in some calculations like sum, average etc.

ii. autonumberhash128() in Qlik Sense

The autonumberhash128() is a variation of the autonumber() function we learned earlier.  Autonumber only created an integer value as the composite key and assigned it to combination of vales but autonumberhash128() first creates a 128-bit hash value for the combination and then assigns an integer number corresponding to each hash value. We create such values to store large and complex keys in less memory space and simpler forms.

Let’s discuss Selections in Qlik Sense

The syntax for Qlik Sense Counter Function autonumebrhash128

autonumberhash128(expression {, expression})

Please note that such we can only create hash values if we use it in the same data load script. If you want to use the composite keys made using autonumberhash128() in some other load script then you must use functions like hash128, hash160, and hash256.

Anyway, to understand this function better, we load a sample data inline and then create a hash value composite key using autonumberhash128() function. In this sample code, we have created a composite key called RYMkey which will assign unique 128-bit hash values to the region, year and month combinations.

RegionSales in autonumberhash128

LOAD *,
AutoNumberHash128(Region, Year, Month) as RYMkey;
LOAD * INLINE
[ Region, Year, Month, Sales
North, 2017, May, 245
North, 2017, May, 347
North, 2017, June, 127
South, 2017, June, 645
South, 2018, May, 367
South, 2018, May, 221
];

This will result in composite keys corresponding to unique combinations of region, year and month.

Now, using these RYMkey values like use 2 if you want to use the combination North, 2017, June. In the code given below, we have used the composite key we have created to load a new field ‘Costs’.

Read Qlik Sense Gauge Chart

RegionCosts:

LOAD Costs,
AutoNumberHash128(Region, Year, Month) as RYMkey;
LOAD * INLINE
[ Region, Year, Month, Costs
South, 2018, May, 167
North, 2017, May, 56
North, 2017, June, 199
South, 2017, June, 64
South, 2018, May, 172
South, 2018, May, 126
];

Now, the sales and Costs fields are automatically linked depending upon the RYM combination they are sharing. For instance, notice in the table below, that shows sum of costs and sales for each unique RYM combination.

iii. autonumberhash256()

The autonumberhash256() function creates a 256-bit value for unique composite keys made for distinct combinations of data records or value. Like, Europe, 2017, January can be one combination of data records for which first a 256-bit hash value will create and then an integer will assign to it. Much like all the other autonumber function, this is also we use to manage system’s space by handling complex keys through making such composite keys. This also prevents from the formation of the synthetic key.

Do you know about Qlik Sense Filter Pane

Syntax for autonumberhash256

autonumberhash256(expression {, expression})

The expressions are the fields from the data record for which you want to create a 256-bit hash.

For instance, if we use the same data set that we have used while explaining autonumberhash128() function, then the code will look like,

RegionSales in Qlik Sense Counter Functions autonumberhash256

LOAD *,
AutoNumberHash256(Region, Year, Month) as RYMkey;
LOAD * INLINE
[ Region, Year, Month, Sales
North, 2017, May, 245
North, 2017, May, 347
North, 2017, June, 127
South, 2017, June, 645
South, 2018, May, 367
South, 2018, May, 221
];

This will create 256-bit hash values and integer values corresponding to the Region, Month and Year combinations the function encounters while executing the script. Like,

• RYMkey 1 for North, 2017, May
• RYMkey 2 for North, 2017, June
• RYMkey 3 for South, 2017, June
• RYMkey 4 for South, 2018, May

Also, there will be unique 256-bit hash values for the key numbers 1,2,3 and 4 stored in the system.

iv. InterNo() in Qlik Sense

The InterNo() function is used to mark or number iterations or number of times an expression is being executed in a loop. Like, the first execution will be marked as 1, second as 2 and so on until the loop is terminated.

Let’s revise Qlik Sense Key Concepts

The syntax for InterNo function:

InterNo()

Let us understand this through an example. Suppose we have a code as below.

LOAD
  IterNo() as Day,
  Date( StartDate + IterNo() - 1 ) as Date
  While StartDate + IterNo() - 1 <= EndDate;
LOAD * INLINE
[StartDate, EndDate
22-01-2018, 26-01-2018
];

This code will generate iterations for each time the loop is executed starting from the start date and ending on the end date as specified in the Load Inline statement. The repetitions will be numbered in the field Day. It is recommended to use the InterNo() function with While clause as it works best in loops.

The above code will generate a table as a result.

This shows that the InterNo() function has created numbers for each time the while loop got executed through the LOAD statement and has stored them as the values of the field, Day. You can use this function for larger data values as it automatically creates number through loop saving the time to manually do it.  

You must read about Qlik Sense capabilities

v. RecNo() in Qlik Sense

The RecNo() function returns the number of record as per each record’s order in the LOAD script. Like, the first record that is read during script execution will be numbered 1, then the next as 2 and so on

The syntax for Qlik Sense Counter Functions RecNo:

RecNo()

For example, we load some data inline through the data load script. We will call this raw data table load.

Table1:

LOAD * INLINE
[A, B
1,cc
2,dd
3,ee];

Table2:

LOAD * INLINE
[C, D
4, xx
5,yy
6,zz];

Now, using RecNo() we will load record numbers of the entries in both the tables loaded above.

QTab:

LOAD *,
RecNo( ),
resident Table1 where A<>2;
LOAD
C as A,
D as B,
RecNo( ),
resident Table2 where A<>4;

//We don’t need the source tables anymore, so we drop them

Do you know about Qlik Sense Box Plot Visualizations

Drop tables Table1, Table2;

Here, we don’t want the record number 2 and 4 from each table. So, the resultant table would be,

If you notice carefully, you’ll observe the record numbers according to the load order in the raw script and not as they appear in the table.

vi. RowNo()

The RowNo() function returns the numbering for rows in the internal Qlik Sense table made by loading data into data load script. This is different from RecNo() function as it returns the numbering of rows when a table is still in the load script in raw form and not in the resultant table.

The syntax for Qlik Sense Counter Functions RowNo:

RowNo([TOTAL])

Let us take the same example that we did in the RecNo() function explanation.

Table1:

LOAD * INLINE
[A, B
1,cc
2,dd
3,ee];

Table2:

Let’s discuss how to create Qlik Sense Applications

LOAD * INLINE
[C, D
4, xx
5,yy
6,zz];

Now, we will create an internal table where we number the rows using RowNo().

QTab:

LOAD *,
RowNo( ),
resident Table1 where A<>2;
LOAD
C as A,
D as B,
RowNo( ),
resident Table2 where A<>4;

//We don’t need the source tables anymore, so we drop them

Drop tables Table1, Table2;

The table created will look like this,

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

3. Conclusion

Hence, in this Qlik Sense Counter Function tutorial, we discussed different functions in Qlik Sense. Thus, these were all the counter functions which we use on the records when they encountered during load script processing and execution.

Also, share your experience of learning Qlik Sense Counter Function through comments.

See also – 

Qlik Sense Conditional Functions

Reference for Qlik Sense