Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to add counter using multiple dimensions during script load. Please see the data below:
A | B | C | Counter |
AA | 11 | 1 | 1 |
AA | 12 | 2 | 2 |
BB | 21 | 4 | 1 |
BB | 22 | 5 | 2 |
BB | 23 | 6 | 3 |
I need to use the counter column to then filter for the top N by Column A. I have tried using AutoNumber(), however, am not able to restart the counter by column A as dimension.
Any help will be appreciated. Thank you!
Regards, Abhishek.
hi
your script should be something like :
assuming you already load the table let call it Table1
than your script will be
Table2:
load *,
if(previous(A)=A, rangesum(peek('Counter'),1),1) as Counter
resident Table1
order by A;
drop table Table1;
hi
your script should be something like :
assuming you already load the table let call it Table1
than your script will be
Table2:
load *,
if(previous(A)=A, rangesum(peek('Counter'),1),1) as Counter
resident Table1
order by A;
drop table Table1;
Thanks you Lironbaram!
Was able to resolve this.
One complexity that I did not highlight was that Column C had repeated values. For example, there can values of A which have the same rank of C. See below:
AA,11,1
AA,12,1
AA,13,2
BB,22,5
BB,23,6
Following is the solution that I used for resolving this issue. Created a rank column and then created a counter column.
Load *,
AutoNumber(Row(), A) as Counter;
Load *,
IF(A <> Previous(A),1,IF(C <> Previous(C), Peek(Rank) + 1, Peek(Rank))) as Rank
Resident Table1
Order By A, C asc;
Regards, Abhishek.