topic Re: Extract Work from string in QlikView

Extract Work from string

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

I have a column called lables,

In that column there are several words

ex - lable1,deydfey,rfr,lable2

fgrfgr,rgrtt,lable1

here i need to find if 'lable1' exsist in each raw and store the value lable1 in separate column name lable1 when loading the script

Re: Extract Work from string

Gysbert_Wassenaar — Tue, 15 Mar 2016 13:47:55 GMT

LOAD

if(substringcount(MyString, 'lable1')>0, 'lable1') as MyField,

...other fields

FROM

...mysource

;

Re: Extract Work from string

sunny_talwar — Tue, 15 Mar 2016 13:53:13 GMT

This should also work, right Gysbert?

If(Index(MyString, 'lable1') > 0, 'lable1') as MyField

Re: Extract Work from string

anuradhaa — Tue, 15 Mar 2016 13:59:15 GMT

what happen if i want to find lable* in that string and store lable* value under the column lable

Re: Extract Work from string

sunny_talwar — Tue, 15 Mar 2016 14:03:56 GMT

May be this:

If(WildMatch(MyString, 'Lable*'), 'lable' & Left(SubField(MyString, 'Lable', 2), 1)) as MyNewField

Re: Extract Work from string

Gysbert_Wassenaar — Tue, 15 Mar 2016 14:05:08 GMT

Assuming all text strings are comma separated lists you can do something like this:

LOAD

MySubstring as MyField,

...other fields...

WHERE Left(MySubstring, 5) = 'lable';

LOAD

subfield(MyString, ',') as MySubString,

...other fields...

FROM

...

Re: Extract Work from string

anuradhaa — Tue, 15 Mar 2016 18:11:08 GMT

Can you share a sample please

Re: Extract Work from string

anuradhaa — Tue, 15 Mar 2016 18:12:57 GMT

This works but problem is in my column lable we can predict number of characters,

ex - Lable_1

Lable_100

Lable_5454

So we can't have

'lable' & Left(SubField(MyString, 'Lable', 2), 1)