topic Re: How count lines for each group separately in QlikView

How count lines for each group separately

Ilya_Bobrov — Mon, 10 Dec 2018 12:10:21 GMT

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)

Re: How count lines for each group separately

avkeep01 — Mon, 10 Dec 2018 12:18:43 GMT

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

Re: How count lines for each group separately

sunny_talwar — Mon, 10 Dec 2018 12:17:40 GMT

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
];

Re: How count lines for each group separately

Ilya_Bobrov — Mon, 10 Dec 2018 12:57:11 GMT

The second optin helps me! Thanks!