Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kishorj1982
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
javiersassen
Partner - Contributor III
Partner - Contributor III

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
javiersassen
Partner - Contributor III
Partner - Contributor III

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.

Saravanan_Desingh

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

kishorj1982
Creator II
Creator II
Author

Thanks - That worked!!