How to add sequence numbers for "grouped by" records

[#1]

Hi all.

I to create a sequence number for every group of records. Here is an example:

Member ID     Class

111                    A

111                    B

111                    C

222                    G

333                    U

333                    F

...

I need the table to look like the following:

Member ID     Class     Seq

111                    A          1

111                    B          2

111                    C          3

222                    G          1

333                    U          1

333                    F          2

Any ideas?

Thanks a lot!

[#2]

Hi Merav,

Please use aggr function to generate the sequence.

aggr(RowNo(),[Member ID],Class)

[MK_QSL · Accepted solution]

Temp:

Load * Inline

[

  Member ID,     Class

  111,                    A

  111,                    B

  111,                    C

  222,                    G

  333,                    U

  333,                    F

];

Final:

Load

  [Member ID],

  Class,

  IF([Member ID] = Previous([Member ID]), RangeSum(Peek(ID),1),1) as ID

Resident Temp

Order By [Member ID], Class;

Drop Table Temp;

[MarcoWedel]

In the front end, just use RowNo() as expression and[Member ID], Class as dimensions.

[Kushal_Chawda]

There are many ways to do it.

May be like this

Load *, AutoNumber(Class,[Member ID]) as Sequence Inline [

  Member ID,     Class

  111,                    A

  111,                    B

  111,                    C

  222,                    G

  333,                    U

  333,                    F

];

[#6]

Thanks a lot for the quick and detailed reply. Works like a charm 🙂

[#7]

Thank you very much. It will help me do it on the GUI side when I need it.

[MarcoWedel]

Hi,

hope this helps

regards

Marco

[lawrenceiow]

That's great Manish Kachhia, I too had a need to create a sequence number and this works perfectly for me. Do you know, can QlikView turn those sequence numbers into columns - the opposite to a crosstable load?

So, using the Member Id, Class example, I would want to end up with

Member Id	ID 1	ID 2	ID 3
111	A	B	C
222	G
333	U	F