topic Re: How to use subfield function on this field? in QlikView

How to use subfield function on this field?

jim_chan — Wed, 11 Jan 2017 05:33:54 GMT

Hi there,

i have a field customercode.

for example i have ABCDJIM. and now i want to use subfield to get 'JIM' only.

how should i write using subfield?

SubField( customercode, 3,1) as Name_only,

Rgds

Jim

avinashelite — Wed, 11 Jan 2017 05:37:25 GMT

To use Subfield() function you need a specific separator ..In your case I don't see any valid separator .

May for your case(if the you know the place value of the text to fetch) you can use the Right function

Right(customercode,3)

Gysbert_Wassenaar — Wed, 11 Jan 2017 05:37:28 GMT

What's the logic to get the name?

Is it always the last three characters: Right(customercode, 3) as Name_only

Or does the name always start on position 5 and the first 4 characters need to be discarded? Mid(customercode,5) as Name_only

jim_chan — Wed, 11 Jan 2017 05:44:02 GMT

sorry. my apology on the example given.

the logic here should be always AFTER 1st 3 characters

Anonymous — Wed, 11 Jan 2017 05:45:01 GMT

If this format is consistantly ABCD infront of the name,

To use substring to Return JIM:

Below we use D as the delimiter and returns the 2nd occurance.

subfield('ABCDJIM', 'D' ,2)

in your case try:

subfield(customercode, 'D' ,2)

I dont feel this is the best way but the above should answer your question.

Please provide some sample data if the values do not have ABCD consistantly appended to the front of the name.

Anonymous — Wed, 11 Jan 2017 05:48:56 GMT

I would use

=right('ABCDJIM', len('ABCDJIM') -4)

=right(customercode, len(customercode) -4)

If there is constantly 3 characters in front

jim_chan — Wed, 11 Jan 2017 05:49:03 GMT

in my case. i have used MID() . thank you.