# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for
Did you mean:
Highlighted
Creator II

## Unique Numbering for each group

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"

 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

1 Solution

Accepted Solutions
Highlighted
Partner

have a look at the following script.

Group:
*,
Change + IF(RowNo()>1,Peek(Group),0) as Group;
*,
IF(ID=Previous(ID) AND Status = Previous(Status),0,1) as Change;
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.

3 Replies
Highlighted
Partner

have a look at the following script.

Group:
*,
Change + IF(RowNo()>1,Peek(Group),0) as Group;
*,
IF(ID=Previous(ID) AND Status = Previous(Status),0,1) as Change;
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.

Highlighted
Specialist III

Check your data. The Group 5 is wrong if you are considering ID & Status as Key.

One solution is.

``````tab1:
;
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
];``````

Highlighted
Creator II

Thanks - That worked!!