Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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!
Genius.
Thank you Manuel!