Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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 ];
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
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 ];
The second optin helps me! Thanks!