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

[FelipeB]

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.

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.

[diegozecchini]

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]

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?

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