How count lines for each group separately

[Ilya_Bobrov]

Good Day!

There is the task of calculating the achievement of target values for each of the reporting centers.

But i have problem: 

rowno() function count all lines like this (thats incorrest)!

load * inline [
reporting_centers, id, targetcounter
1,some_Uniqvalue,1
1,some_Uniqvalue,2
1,some_Uniqvalue,3
2,some_Uniqvalue,4
2,some_Uniqvalue,5
3, some_Uniqvalue,6
3, some_Uniqvalue,7
3, some_Uniqvalue,8
];

I managed to solve the problem by breaking a single array into several separate ones, followed by writing them to different files and only after that I got the necessary cut.

But it seems to me that there is a simpler solution to this problem.

Sample of data:

load * inline [
reporting_centers, id
1,some_Uniqvalue
1,some_Uniqvalue
1,some_Uniqvalue
2,some_Uniqvalue
2,some_Uniqvalue
3, some_Uniqvalue
3, some_Uniqvalue
3, some_Uniqvalue
];

I need this result:

load * inline [
reporting_centers, id, targetcounter
1,some_value, 1
1,some_value,2
1,some_value,3
2, some_value, 1
2, some_value, 2
3, some_value, 1
3, some_value, 2
3, some_value, 3
]; and etc (I have 21 reporting centers for a total of two million records) 

[avkeep01]

Hi, 

You could look at this solution: 

https://community.qlik.com/t5/QlikView-Scripting/rank-in-script/m-p/1517994#M93286

Sort you table based on your count and then add: 

IF(ROWNO()=1,1,PEEK('targetcounter',-1)+1) AS targetcounter

It peeks at the previous rows and adds 1 to that number. When sorted at the correct count field you'll get a Rank. 

If you want to start a new count for every ID use this: 

IF(PEEK('reporting_centers',-1)<>ID,1,PEEK('targetcounter',-1)+1) AS targetcounter

[sunny_talwar · Accepted solution]

Here are the two options

Table:
LOAD *,
	 If(reporting_centers = Previous(reporting_centers), RangeSum(Peek('targetcounter'), 1), 1) as targetcounter,
	 AutoNumber(RowNo(), reporting_centers) as targetcounter2;
LOAD * INLINE [
    reporting_centers, id
    1, some_Uniqvalue
    1, some_Uniqvalue
    1, some_Uniqvalue
    2, some_Uniqvalue
    2, some_Uniqvalue
    3, some_Uniqvalue
    3, some_Uniqvalue
    3, some_Uniqvalue
];

[Ilya_Bobrov]

The second optin helps me! Thanks!