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

[FelipeB]

I need to create a query on Qlik where it will return me the numbers of occurences from a specific string before the line it was inserted by user.

I've already found the solution using SQL classic, which is the query below:

SELECT *,

       (SELECT COUNT(*)

        FROM Base AS T2

        WHERE T2.[STRINGCONCATENATION] = T1.[STRINGCONCATENATION]

        AND T2.[RowNumber] <= T1.[RowNumber]) AS OccurrenceCount

FROM Base AS T1

ORDER BY [RowNumber];

Rownumber is literally the row's number from each data from database

and STRINGCONCATENATION, as its name says, its a concatenation of many columns.

I've been stuck with this for few days, I've already tried the below query 

How do I convert the above SQL classical query into Qlik Query?

It would be so much easier if we were able use the SQL classical query

Ps: my database is a Excel file.

[Clement15]

Hello,
I'm not sure I understand what you want but if that's the case, you can follow the following example to count the number of occurrences of a line. To customize it, you just have to choose all the fields that you want to take into account in the autonumber.

TEST:
load 
[Supplier name],
[Service charge key],
Amount,
AutoNumber([Supplier name]&[Service charge key] & [Amount]) as IDRow

Inline [
Supplier name, Service charge key, Amount
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
];


Left Join(TEST)
load
IDRow,
Count(IDRow) as CountRow
Resident TEST
Group by IDRow;

[FelipeB]

Greetings. Thank you for you help. 

What I need is how many ocurrences any of the concat field ID has untill any line of database. If a specific concat ID has 5 ocurrences in total, I need to show the count for each appereance: 1, 2, 3, 4 and 5.

The formula that you wrote counts the total of occurences. So the answer that I'm looking for must be:

Supplier name, Service charge key, Amount, CountRow
Supplier A, 52-Location, 1000, 1
Supplier B, 54-contract, 4000, 1
Supplier C, 60-financial, 5000, 1
Supplier A, 52-Location, 1000, 2 
Supplier B, 54-contract, 4000, 2 
Supplier C, 60-financial, 5000, 2
Supplier A, 52-Location, 1000, 3 
Supplier B, 54-contract, 4000, 3 
Supplier B, 54-contract, 4000, 4
Supplier C, 60-financial, 5000, 3
Supplier B, 54-contract, 4000, 5 
Supplier C, 60-financial, 5000, 4
Supplier B, 54-contract, 4000, 6 
Supplier C, 60-financial, 5000, 5 

[Clement15 · Accepted solution]

Hello,

So this should work

TEST:
load 
[Supplier name],
[Service charge key],
Amount,
AutoNumber([Supplier name]&[Service charge key] & [Amount]) as IDRow

Inline [
Supplier name, Service charge key, Amount
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier A, 52-Location, 1000 
Supplier B, 54-contract, 4000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
Supplier B, 54-contract, 4000 
Supplier C, 60-financial, 5000 
];

NoConcatenate
Test2:
load
*,
if(IDRow=previous(IDRow),peek(CountRow)+1,1) as CountRow
Resident TEST
Order by IDRow;

Drop Table TEST;

[FelipeB]

Thank you so much! I've been trying to create this query for 10 days at least. 

[Clement15]

You're welcome, it can take time to learn the Qlik functions and be able to combine them.

[Kushal_Chawda]

@FelipeB  If I understood it correctly below should work. If it doesn't, share sample data with expected output

LOAD *,
    autonumber(RowNumber,STRINGCONCATENATION) as Count
FROM table;