Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a requirement where I need to add unique numbers based on Individual groups ID + Status ; if group is repeated then the counter should change. I need outcome as mentioned in Column "group"
Can someone please help ?
ID | Status | Group |
11111 | ABC | 1 |
11111 | ABC | 1 |
11111 | ABC | 1 |
11111 | CC | 2 |
11111 | DD | 3 |
11111 | XYZ | 4 |
11111 | XYZ | 4 |
11111 | XYZ | 4 |
11111 | ABC | 5 |
11111 | ABC | 5 |
11111 | ABC | 5 |
11111 | ABC | 5 |
11111 | ABC | 5 |
have a look at the following script.
Group:
LOAD
*,
Change + IF(RowNo()>1,Peek(Group),0) as Group;
LOAD
*,
IF(ID=Previous(ID) AND Status = Previous(Status),0,1) as Change;
LOAD * INLINE [
ID, Status
11111, ABC
11111, ABC
11111, ABC
11111, CC
11111, DD
11111, XYZ
11111, XYZ
11111, XYZ
11111, ABC
11111, ABC
11111, ABC
11111, ABC
11111, ABC];
So when the values of both ID and Status aren't the same as the previous row, change becomes 1. Next you're going to calculate rolling totals in which you use the value Group of the previously loaded row and add the value of change to it.
have a look at the following script.
Group:
LOAD
*,
Change + IF(RowNo()>1,Peek(Group),0) as Group;
LOAD
*,
IF(ID=Previous(ID) AND Status = Previous(Status),0,1) as Change;
LOAD * INLINE [
ID, Status
11111, ABC
11111, ABC
11111, ABC
11111, CC
11111, DD
11111, XYZ
11111, XYZ
11111, XYZ
11111, ABC
11111, ABC
11111, ABC
11111, ABC
11111, ABC];
So when the values of both ID and Status aren't the same as the previous row, change becomes 1. Next you're going to calculate rolling totals in which you use the value Group of the previously loaded row and add the value of change to it.
Check your data. The Group 5 is wrong if you are considering ID & Status as Key.
One solution is.
tab1:
LOAD *, AutoNumber(ID&Status) As Group
;
LOAD RecNo() As RowID,* INLINE [
ID, Status
11111, ABC
11111, ABC
11111, ABC
11111, CC
11111, DD
11111, XYZ
11111, XYZ
11111, XYZ
11112, ABC
11112, ABC
11112, ABC
11112, ABC
11112, ABC
];
Thanks - That worked!!