Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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;
Load ID, Date, Cost,
Autonumber(RecNo(), ID) as counter
Form source
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
it seems the best solution, no table to drop, just one statement
thank you
.
Thanks everyone for your quick responses and assistance!