autoincrement with rowno() problem

[#1]

hi everybody

is there a way to autoincrement (rowno() function) a column by group like:

I have:

storeid | store | rowno()
------------------------
3 | store3 | 1
3 | store3 |     2
3 | store3 | 3
3 | store3 | 4
7 | store7 | 5
7 | store7 | 6
7 | store7 | 7

I need:

storeid | store | rowno()
------------------------
3 | store3 | 1
3 | store3 |     2
3 | store3 | 3

3 | store3 | 4
7 | store7 | 1
7 | store7 | 2
7 | store7 | 3

Thx in advance

[CELAMBARASAN]

Hi,

     There is no predefined function for this i think.Try this approach.

     Table1:

     Load

          storeid,

          store

     From DataSource;

     Table2:

     Load Distinct

          storeid

     Resident Table1;

FOR i=1 to NoOfRows('Table2')

LET vStoreid=FieldValue('storeid',$(i));

Let vRows = Alt(NoOfRows('Output'),0);

Output:

LOAD

          storeid,

          store,

          RowNo()-$(vRows) as rowno

Resident

          Table1 Where storeid=$(vStoreid);

NEXT i;

Drop Table Table2;

Celambarasan

Edited: Editing problem.Check now. Message was edited by: Celambarasan Adhimulam

[#3]

Hi Celambarasan,

thx for answering.

But I dont understand your line:
TableTemp Where storeid='$(vStoreid)';  // ---> what is TableTemp and why the 'where' clause?

cheers

PQI

[#4]

Hi, try this code

Temp:

LOAD * INLINE [

storeid, store

3, store3

3, store3

3, store3

3, store3

7, store7

7, store7

7, store7

7, store7

];

Tab:

LOAD

storeid,

store,

IF (peek('storeid')<>storeid,1, peek('counter')+1) AS counter

RESIDENT Temp;

drop table Temp;

[#5]

Przemek K schrieb:

Hi, try this code

Temp:

LOAD * INLINE [

storeid, store

3, store3

3, store3

3, store3

3, store3

7, store7

7, store7

7, store7

7, store7

];

Tab:

LOAD

storeid,

store,

IF (peek('storeid')<>storeid,1, peek('counter')+1) AS counter

RESIDENT Temp;

drop table Temp;

My Result for now is not what I want:

StoreID | Store | Counter

x_abc | abc | 1

y_abc | abc | 1
z_abc | abc | 1
q_efg  | efg  | 1

p_efg  | efg  | 1

r_efg   | efg  | 1

t_hij    | hij   | 1
s_hij   | hij   | 1

But I need:

StoreID | Store | Counter

x_abc | abc | 1

y_abc | abc | 2
z_abc | abc | 3
q_efg  | efg  | 1

p_efg  | efg  | 2

r_efg   | efg  | 1

t_hij    | hij   | 2
s_hij   | hij   | 3

Thx for helping!

[#6]

Change the code to

Tab:

LOAD

storeid,

store,

IF (peek('store')<>store,1, peek('counter')+1) AS counter

RESIDENT Temp;

Regards

[CELAMBARASAN · Accepted solution]

Hi,

   You can try with this

Tab:

LOAD

storeid,

store,

IF (previous(store)<>store,1, previous(counter)+1) AS counter

RESIDENT Temp;

Celambarasan

[jagan]

Hi,

Use this script

Temp:

LOAD * INLINE [

storeid, store

3, store3

3, store3

3, store3

3, store3

7, store7

7, store7

7, store7

7, store7

];

Tab:

LOAD

storeid,

store,

IF (previous(storeid)<>storeid,1, previous(counter) + 1) AS counter

RESIDENT Temp

ORDER BY storeid;

Regards,

Jagan.