topic Re: String separation in QlikView

String separation

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a text string with three information "MLB B/C/AU491_2022"

Platform: MLB B/C

Program: AU491

SOP: 2022

Separators are '/' and '_'

I have tried to use TextBetween, but as you can see in some strings the separator / is uses twice.

How can I script it to get three separate strings?

more examples of the string:

MRA MID-SIZE/W206_2021

MLB B/C/AU491_2022

GA-K/480B_2019

Many thanks!

jens

Re: String separation

Chanty4u — Fri, 14 Jul 2017 10:03:16 GMT

what is your expected output?

Re: String separation

Chanty4u — Fri, 14 Jul 2017 10:08:03 GMT

Hi,

check the posibility

load * Inline [

text

MRA MID-SIZE/W206_2021

MLB B/C/AU491_2022

GA-K/480B_2019

];

Result:

LOAD *,

PurgeChar(text,'-/_') as total,

KeepChar(text,'ABCDEFGHIJKLMNOPQRSTUVWXYX') AS string,

KeepChar(text,'0123456789') AS Number

Resident a;

Re: String separation

tresB — Fri, 14 Jul 2017 10:11:34 GMT

To handle that multiple separator you could use third parameter of textbetween(), like:

=TextBetween(String, '/', '_', SubStringCount(String,'/'))

Re: String separation

jaumecf23 — Fri, 14 Jul 2017 10:12:59 GMT

Maybe first you can use the function Replace(). To replace when a delimiter will be '_' to '/'. Using this trick you will have always a unique delimiter. Replace(Field,'_','/')

After that there is a function called SubField(), that need three parameters. This function splits the field provided using the delimiter and then the defined position is returned:

SubField(Field, Delimiter,indexPosition)

You can use something like this:

Platform: SubField(Field, '/',1)

Program: SubField(Field, '/',2)

SOP: SubField(Field, '/',3)

Re: String separation

effinty2112 — Fri, 14 Jul 2017 10:57:24 GMT

Hi Jens,

If vStr is the input then:

Platform : left('$(vStr)',len('$(vStr)')- len(SubField( '$(vStr)','/',-1))-1)

Program : subfield(SubField( '$(vStr)','/',-1),'_',1)

SOP :subfield(SubField( '$(vStr)','/',-1),'_',-1)

Input	Platform	Program	SOP
GA-K/480B_2019	GA-K	480B	2019
MRA MID-SIZE/W206_2021	MRA MID-SIZE	W206	2021
MLB B/C/AU491_2022	MLB B/C	AU491	2022

Cheers

Andrew

Re: String separation

Fri, 14 Jul 2017 11:25:26 GMT

Many thanks to all of you.

I will check the different solutions.

Regards

Jens

Re: String separation

jmvilaplanap — Fri, 14 Jul 2017 12:26:54 GMT

Hi,

If the field is a concatenation of another fields made by you or anyone can made changes in the data source, I suggest change the separation char, because this char can be used inside a field.

For example:

"MLB B/C#AU491#2022"

And use the function Subfield to get all this three fields

Field1 = subfield(Field, '#', 1)
Field2 = subfield(Field, '#', 2)
Field3 = subfield(Field, '#', 3)