- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actually it didn't work. I've found the solution using classical SQL query:
And please, before you use any chatgpt or copilot answers, test it before you post.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you have posted this earlier:
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
];