topic Re: Numbering a Subfield within the original ID in QlikView

Numbering a Subfield within the original ID

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Numbering a Subfield within the original ID

swuehl — Wed, 01 Jun 2016 15:19:47 GMT

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

Re: Numbering a Subfield within the original ID

Anonymous — Wed, 01 Jun 2016 15:29:00 GMT

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!

Re: Numbering a Subfield within the original ID

Wed, 01 Jun 2016 15:41:56 GMT

Genius.

Re: Numbering a Subfield within the original ID

Wed, 01 Jun 2016 15:42:21 GMT

Thank you Manuel!