Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
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" 

Can someone please help ? 

IDStatusGroup
11111ABC1
11111ABC1
11111ABC1
11111CC2
11111DD3
11111XYZ4
11111XYZ4
11111XYZ4
11111ABC5
11111ABC5
11111ABC5
11111ABC5
11111ABC5

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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.

View solution in original post

3 Replies
Highlighted
Partner
Partner

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.

View solution in original post

Specialist III
Specialist III

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

commQV35.PNG

Highlighted
Creator II
Creator II

Thanks - That worked!!