Rank number that resets based on values of a column

[ali_hijazi]

Dear

I got the following sample data:

Visit, Location

1,L1

1,L2

1,L3

2,L4

2,L5

3,L6

what I want is to add a third column which will an autonumber that resets on each new value of Visit

i.e.

Visit, Location, Rank

1,L1,1

1,L2,2

1,L3,3

2,L4,1

2,L5,2

3,L6,1

[swuehl]

LOAD

     Visit,

     Location,

    autonumber(recno(), Visit) as Rank

FROM ...;

[ali_hijazi]

it doesn't give the desired result

[ali_hijazi]

and here is the result of your suggested solution:

[swuehl]

Can't reproduce your results,

LOAD *,

          AutoNumber(recno(), Visit) as Rank

INLINE [

Visit, Location

1,L1

1,L2

1,L3

2,L4

2,L5

3,L6

];

produces your requested outcome. Please post a sample application.

[jolivares]

Try this...

T1:

LOAD * INLINE [

    F1, F2

    1, L1

    1, L2

    1, L3

    2, L2

    2, L4

    3, L5

    3, L7

];

T2:

Load *,

     If(RecNo()=1,1,If(Previous(F1)=F1,Peek(Rank)+1,1)) as Rank

Resident T1

Order By F1;

Drop Table T1;

[jolivares · Accepted solution]

Better:

If(Previous(F1)=F1,Peek(Rank)+1,1) as Rank,