Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
FelipeB
Contributor II

Count how many times record has appeared before (including actual row) in LOAD section

Hello there.

I need to create a column in LOAD section to count how many times record has appeared before (including actual row).

I created a function called "UNIQUE-AUTO" on LOAD section

AutoNumber(Upper([Column1] & [Column2] & [Column3] & [Column4] & [Column5] & [Column6] & [Column7] & [Column8])) as "UNIQUE-AUTO"

This function aims to give a specific number for the concatenation of several columns.

So if I filter for the number 21454, I see that there are 5 occurrences in my database.

FelipeB_0-1734026635854.png

What I need is to create a function in SQL to find out how many times the record from UNIQUE-AUTO column appeared before. I.E, the values from the new calculated collum in SQL must show in the first line 1, the second line 2 and so on up to 5, as there are 5 occurrences for the number 21454.

 

Labels (1)
5 Replies
diegozecchini
Creator III

Hi!
To achieve this functionality in SQL, you can use the ROW_NUMBER() window function along with PARTITION BY to create a calculated column that counts occurrences of each value in the UNIQUE-AUTO column.

SELECT
*,
ROW_NUMBER() OVER (PARTITION BY [UNIQUE-AUTO] ORDER BY [UNIQUE-AUTO], [SomeOtherColumn]) AS OccurrenceCount
FROM YourTableName

ROW_NUMBER(): This assigns a sequential number to rows within a partition. The partition is based on the UNIQUE-AUTO column.
PARTITION BY: Divides the data into groups based on the values in the UNIQUE-AUTO column. Each group will have its own numbering starting from 1.
ORDER BY: Specifies the order in which the rows should be numbered. Replace [SomeOtherColumn] with a column that ensures the desired ordering (e.g., a timestamp column or another relevant field).

Example
Input Table
UNIQUE-AUTO SomeOtherColumn OtherData
21454 2023-01-01 ABC
21454 2023-01-02 DEF
21454 2023-01-03 GHI
21454 2023-01-04 JKL
21454 2023-01-05 MNO

Result
UNIQUE-AUTO SomeOtherColumn OtherData OccurrenceCount
21454 2023-01-01 ABC 1
21454 2023-01-02 DEF 2
21454 2023-01-03 GHI 3
21454 2023-01-04 JKL 4
21454 2023-01-05 MNO 5

This approach ensures that you calculate the occurrence count dynamically and works with any SQL database supporting window functions

FelipeB
Contributor II
Author

Thank you. Dont get me wrong, I'm newbie into Qliksense, but why does my Qliksense doesn't reconigze any of the classical SQL function?

FelipeB_0-1734032306546.pngFelipeB_1-1734032336355.png

TABLE1 is located on "Seção 2", but I can't make connection between them on "Seção"

diegozecchini
Creator III

Hi!
Sorry I thought you were using SQL!!
To implement the functionality you need within QlikSense, you must use Qlik's script syntax instead of SQL directly, as QlikSense has its own script language and does not support native SQL window functions like ROW_NUMBER().

You can create a column that tracks the count of occurrences of a specific value (e.g., your UNIQUE-AUTO column) using the Peek() or Previous() function in a load script.

Something like this
LOAD
*,
RowNo() AS RowNumber, // Optional: Add row numbers if needed
AutoNumber(Upper(Column1 & Column2 & Column3 & Column4 & Column5 & Column6 & Column7 & Column8)) AS [UNIQUE-AUTO]
RESIDENT SourceTable;

LOAD
*,
RangeSum(Peek('OccurrenceCount'), 1) AS OccurrenceCount
RESIDENT SourceTable
ORDER BY [UNIQUE-AUTO], [SomeOtherColumn];


AutoNumber Function: Combines multiple fields to create a unique value, as you've already done.
RangeSum and Peek: Used to calculate the occurrence count incrementally. Peek checks the previous row's occurrence count and adds 1.
ORDER BY Clause: Ensures that the rows are processed in the correct order for counting occurrences within each group.

let me know, kind regards,
Diego

FelipeB
Contributor II
Author

Actually it didn't work. I've found the solution using classical SQL query:

SELECT *,
       (SELECT COUNT(*)
        FROM Base AS T2
        WHERE T2.[UNIQUE-AUTO] = T1.[UNIQUE-AUTO]
        AND T2.[RowNumber] <= T1.[RowNumber]) AS OccurrenceCount
FROM Base AS T1
ORDER BY [RowNumber];
 
How do I convert the above classical SQL query into "Qlik SQL"?
 
I just want to create a new table using the above query. 
 
FelipeB_1-1734543217277.png

 

And please, before you use any chatgpt or copilot answers, test it before you post. 

 

Qrishna
Master

you have posted this earlier:

https://community.qlik.com/t5/New-to-Qlik-Analytics/Count-the-number-of-occurrences-above-string/td-...

 

 i have already give a backedn solution.

use: AutoNumber(ConcatNCM&'-'&Numer,ConcatNCM) as runningcount

in your load script.

//--------------------------------------------------------------------------------------//

My solution from the previous post:

A simple one liner code using load editor script. No need of any complex front end expressions.

TABLE_TEMP:
load *,
AutoNumber(ConcatNCM&'-'&Numer,ConcatNCM) as runningcount
inline [
Numer, ConcatNCM
1001,AAA
2,BBB
3,CCC
4,DDD
1002,AAA
6,CCC
7,CCC
8,EEE
9,BBB
10,CCC
11,CCC
12,EEE
1003,AAA
14,BBB
15,EEE
16,BBB
1004,AAA
18,AAA
19,CCC
20,BBB
];

2494046 - Count the number of occurrences above (string) (1).PNG