Autogenerate question

[cbushey1]

I think I need to use autogenerate but I am not sure how to get this to work.

I have a file that has multiple iterations of the same ID (one per line). This ID relates to multiple transactions, hence the different rows.

I need to be able to number each row per ID.

Example

ID     Date          Cost     #

A     11/25/14     50.00     1

B     11/20/14     99.00     1

B     11/21/14     3.55       2

C     11/25/14     6.52       1

The '#' above should always start at 1 and count the number of times my ID number appears.

Thoughts?    

[simenkg]

Temp:

Load * from table.qvd (qvd);

Data:

load *,

if(Peek(ID)=ID,rangesum(Peek(#),1), 1) as #

resident Temp

Order by ID asc;

Drop table Temp;

[maxgro · Accepted solution]

a:

load * inline [

ID ,    Date    ,      Cost  ,   #

A  ,   11/25/14 ,    50.00,     1

B  ,   11/20/14 ,    99.00,     1

B  ,   11/21/14 ,    3.55 ,      2

C  ,   11/25/14 ,    6.52 ,      1

];

b:

NoConcatenate

load ID, Date, Cost, if(peek(ID)<>ID, 1, peek(counter) +1) as counter resident a  order by ID, Date;

drop table a;

[MarcoWedel]

Load ID, Date, Cost,

           Autonumber(RecNo(), ID) as counter

Form source

[MarcoWedel]

like this:

LOAD *,
    AutoNumber(RecNo(), ID) as #
INLINE [
    ID, Date, Cost
    A, 11/25/14, 50.00
    B, 11/20/14, 99.00
    B, 11/21/14, 3.55
    C, 11/25/14, 6.52
];

hope this helps

regards

Marco

[maxgro]

it seems the best solution, no table to drop, just one statement

[MarcoWedel]

thank you

.

[cbushey1]

Thanks everyone for your quick responses and assistance!