{"id":39599,"date":"2018-11-09T15:08:46","date_gmt":"2018-11-09T09:38:46","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=39599"},"modified":"2018-11-09T15:08:46","modified_gmt":"2018-11-09T09:38:46","slug":"qlik-sense-counter-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/qlik-sense-counter-functions\/","title":{"rendered":"Qlik Sense Counter Functions With Syntax"},"content":{"rendered":"

1. Objective – Counter Functions in Qlik Sense<\/span><\/h2>\n

In our last Qlik Sense Tutorial<\/strong>, we discussed Qlik Sense Pie Chart<\/a><\/strong>. 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.\u00a0<\/span><\/p>\n

So, let’s start the Qlik Sense Counter Functions Tutorial.<\/p>\n

\"Qlik<\/a>

Qlik Sense Counter Functions With Syntax<\/p><\/div>\n

You must read about Qlik Sense Color Functions<\/a><\/strong><\/p>\n

2. Qlik Sense Counter Functions<\/h2>\n

Following are some Counter Functions in Qlik Sense –<\/p>\n

i. autonumber() in Qlik Sense<\/span><\/h3>\n

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

The syntax for Qlik Sense aurtonumber function –\u00a0<\/strong><\/p>\n

autonumber(expression[ , AutoID])<\/pre>\n
where <\/span>expression<\/span><\/i> is the record names that you want to make composite keys for using autonumber.<\/span><\/p>\n
Basically, the <\/span>AutoID<\/span><\/i> 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.<\/span><\/p>\n
For example, we have created a composite key for the records of combination of the fields Region, Year and Month. <\/span><\/p>\n
RegionSales\u00a0in Qlik Sense autonumber:<\/strong><\/p>\n
LOAD *,\r\nAutoNumber(Region&Year&Month) as RYMkey;\r\nLOAD * INLINE\r\n[ Region, Year, Month, Sales\r\nNorth, 2017, May, 245\r\nNorth, 2017, May, 347\r\nNorth, 2017, June, 127\r\nSouth, 2017, June, 645\r\nSouth, 2018, May, 367\r\nSouth, 2018, May, 221\r\n];<\/pre>\n
So, this composite key will assign integers to the records of the table we loaded inline.<\/span><\/p>\n
Have a look at Qlik Sense Histogram Visualization<\/a><\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Region<\/strong><\/td>\nYear<\/strong><\/td>\nMonth<\/strong><\/td>\nSales<\/strong><\/td>\nRYMkey<\/strong><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nMay<\/span><\/td>\n245<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nMay<\/span><\/td>\n374<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nJune<\/span><\/td>\n127<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2017<\/span><\/td>\nJune<\/span><\/td>\n645<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nMay<\/span><\/td>\n367<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nMay<\/span><\/td>\n221<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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. <\/span><\/p>\n
ii. autonumberhash128() in Qlik Sense<\/span><\/h3>\n
The autonumberhash128() is a variation of the autonumber() function we learned earlier. \u00a0Autonumber 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.<\/span><\/p>\n
Let’s discuss Selections in Qlik Sense<\/a><\/strong><\/p>\n
The syntax for Qlik Sense Counter Function autonumebrhash128<\/strong><\/p>\n
autonumberhash128(expression {, expression})<\/pre>\n
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. <\/span><\/p>\n
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. <\/span><\/p>\n
RegionSales\u00a0in autonumberhash128<\/strong><\/p>\n
LOAD *,\r\nAutoNumberHash128(Region, Year, Month) as RYMkey;\r\nLOAD * INLINE\r\n[ Region, Year, Month, Sales\r\nNorth, 2017, May, 245\r\nNorth, 2017, May, 347\r\nNorth, 2017, June, 127\r\nSouth, 2017, June, 645\r\nSouth, 2018, May, 367\r\nSouth, 2018, May, 221\r\n];<\/pre>\n
This will result in composite keys corresponding to unique combinations of region, year and month.<\/p>\n\n\n\n\n\n\n\n\n\n
Region<\/strong><\/td>\nYear<\/strong><\/td>\nMonth<\/strong><\/td>\nSales<\/strong><\/td>\nRYMkey<\/strong><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nMay<\/span><\/td>\n245<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nMay<\/span><\/td>\n374<\/span><\/td>\n1<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nJune<\/span><\/td>\n127<\/span><\/td>\n2<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2017<\/span><\/td>\nJune<\/span><\/td>\n645<\/span><\/td>\n3<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nMay<\/span><\/td>\n367<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nMay<\/span><\/td>\n221<\/span><\/td>\n4<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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 \u2018Costs\u2019.<\/span><\/p>\n
Read Qlik Sense Gauge Chart<\/a><\/strong><\/p>\n
RegionCosts:<\/span><\/p>\n
LOAD Costs,\r\nAutoNumberHash128(Region, Year, Month) as RYMkey;\r\nLOAD * INLINE\r\n[ Region, Year, Month, Costs\r\nSouth, 2018, May, 167\r\nNorth, 2017, May, 56\r\nNorth, 2017, June, 199\r\nSouth, 2017, June, 64\r\nSouth, 2018, May, 172\r\nSouth, 2018, May, 126\r\n];<\/pre>\n
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. <\/span><\/p>\n\n\n\n\n\n\n\n\n
Region<\/strong><\/td>\nYear<\/strong><\/td>\nMonth<\/strong><\/td>\nSum([Sales])<\/strong><\/td>\nSum([Costs])<\/strong><\/td>\n<\/tr>\n
Totals<\/span><\/td>\n<\/td>\n<\/td>\n1952<\/span><\/td>\n784<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nJune<\/span><\/td>\n127<\/span><\/td>\n199<\/span><\/td>\n<\/tr>\n
North<\/span><\/td>\n2017<\/span><\/td>\nMay<\/span><\/td>\n592<\/span><\/td>\n56<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nJune<\/span><\/td>\n645<\/span><\/td>\n64<\/span><\/td>\n<\/tr>\n
South<\/span><\/td>\n2018<\/span><\/td>\nMay<\/span><\/td>\n588<\/span><\/td>\n465<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
iii. autonumberhash256()<\/span><\/h3>\n
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\u2019s space by handling complex keys through making such composite keys. This also prevents from the formation of the synthetic key.<\/span><\/p>\n
Do you know about Qlik Sense Filter Pane<\/a><\/strong><\/p>\n
Syntax for autonumberhash256<\/strong><\/p>\n
autonumberhash256(expression {, expression})<\/pre>\n
The expressions are the fields from the data record for which you want to create a 256-bit hash.<\/span><\/p>\n
For instance, if we use the same data set that we have used while explaining autonumberhash128() function, then the code will look like,<\/span><\/p>\n
RegionSales\u00a0in Qlik Sense Counter Functions autonumberhash256<\/strong><\/p>\n
LOAD *,\r\nAutoNumberHash256(Region, Year, Month) as RYMkey;\r\nLOAD * INLINE\r\n[ Region, Year, Month, Sales\r\nNorth, 2017, May, 245\r\nNorth, 2017, May, 347\r\nNorth, 2017, June, 127\r\nSouth, 2017, June, 645\r\nSouth, 2018, May, 367\r\nSouth, 2018, May, 221\r\n];<\/pre>\n
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,<\/span><\/p>\n