Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to add sequence numbers for "grouped by" records

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!

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

8 Replies
Not applicable
Author

Hi Merav,

Please use aggr function to generate the sequence.

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

MK_QSL
MVP
MVP

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

];

Not applicable
Author

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

Not applicable
Author

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

MarcoWedel

Hi,

QlikCommunity_Thread_140962_Pic1.JPG.jpg

hope this helps

regards

Marco

lawrenceiow
Creator II
Creator II

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 IdID 1ID 2ID 3
111ABC
222G
333UF