topic Re: How to add sequence numbers for "grouped by" records in QlikView

How to add sequence numbers for "grouped by" records

Wed, 05 Nov 2014 15:59:12 GMT

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!

Re: How to add sequence numbers for "grouped by" records

Wed, 05 Nov 2014 16:10:06 GMT

Hi Merav,

Please use aggr function to generate the sequence.

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

Re: How to add sequence numbers for "grouped by" records

MK_QSL — Wed, 05 Nov 2014 16:11:15 GMT

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;

Re: How to add sequence numbers for "grouped by" records

MarcoWedel — Wed, 05 Nov 2014 17:05:29 GMT

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

Re: How to add sequence numbers for "grouped by" records

Kushal_Chawda — Wed, 05 Nov 2014 17:26:39 GMT

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

];

Re: How to add sequence numbers for "grouped by" records

Wed, 05 Nov 2014 17:31:09 GMT

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

Re: How to add sequence numbers for "grouped by" records

Wed, 05 Nov 2014 17:34:35 GMT

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

Re: Re: How to add sequence numbers for "grouped by" records

MarcoWedel — Wed, 05 Nov 2014 20:01:59 GMT

Hi,

hope this helps

regards

Marco

Re: Re: How to add sequence numbers for "grouped by" records

lawrenceiow — Tue, 11 Nov 2014 12:17:19 GMT

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