Generate row number as per group

[Former Member]

Hi ,

I have one scenario:

A  1

A  1

B  2

B  2

C  3

D  4

Whenever group will change it will increment the number but if it is same keep the old number any idea?.

Thanks!!

Amit

[former_member187605]

Use a global variable ($GV with initial value = 0) and a simple 2-line custom function FN_Incr:

     $GV = $GV + 1;

     return $GV;

Then map your numeric column to:

     ifthenelse (Query.C1 = previous_row_value( Query.C1), $GV, FN_Incr($GV))

Make sure your input is sorted according to C1 before applying this transformation.

[former_member695150]

Hello Dirk,

Your solution is really helpful to me.

But I have new scenario now which is as follows:

A1 B1 C1 --> 1

A1 B2 C1 --> 1

A1 B1 C2 --> 2

A1 B2 C2 --> 2

A1 B1 C3 --> 3

A1 B2 C3 --> 3

A1 B1 C4 --> 4

A1 B2 C4 --> 4

A2 B1 C5 --> 1

A2 B2 C5 --> 1

A2 B1 C6 --> 2

A2 B2 C6 --> 2

A3 B1 C6 --> 1

A3 B2 C6 --> 1

A4 B1 C7 --> 1

A4 B2 C8 --> 2

and so on...

In this way I have to assign row count.

Will you please help me out here?

[Former Member]

Use  function gen_row_num( )