Numbering a Subfield within the original ID

[#1]

Hey guys - hopefully a simple one.

I have the following data:

R_ID	R_Text
1001	Hello/How are you
1002	Hello/How is it going/talk to me
1003	Hello/why are you not talking/this is rude
1004	This is terrible/customer/service

I'd like to subfield it as follows:

LOAD

R_ID,

Subfield(R_Text,'/') as R_Text

FROM etc.

However, I ALSO want to number each line, so the final data looks something like this:

R_ID	R_Text	R_TextLineNo
1001	Hello	1
1001	How are you	2
1002	Hello	1
1002	How is it going	2
1002	talk to me	3
1003	Hello	1

etc.

Any ideas? I tried with recno() and numsum() but couldn't get it working.

Thank you!

Alex

[swuehl · Accepted solution]

Try

LOAD

R_ID,

Subfield(R_Text,'/') as R_Text,

Autonumber(recno(), R_ID) as R_TextLineNo

FROM etc.

edit: Same problem using Recno() here, but you can use RowNo():

Autonumber(RowNo(), R_ID) as R_TextLineNo

[Anonymous]

Hi Alex,

Try this:

Original:

LOAD

R_ID,

Subfield(R_Text,'/') as R_Text,

FROM etc...

Order by R_ID asc;

Last:

LOAD

     R_ID,

     R_Text,

     IF(Previous(R_ID) = R_ID, Peek(R_TextLineNo)+1,1) as R_TextLineNo

RESIDENT Original;

Regards!

[#4]

Genius.

[#5]

Thank you Manuel!