topic Re: Unique Numbering for each group in QlikView

Unique Numbering for each group

kishorj1982 — Sat, 02 May 2020 04:37:21 GMT

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

Re: Unique Numbering for each group

javiersassen — Sat, 02 May 2020 07:24:46 GMT

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.

Re: Unique Numbering for each group

Saravanan_Desingh — Sat, 02 May 2020 13:46:48 GMT

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

Re: Unique Numbering for each group

kishorj1982 — Sun, 03 May 2020 17:09:39 GMT

Thanks - That worked!!