Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

priqvqlik
Contributor

sub String

Please help with getting substring from multiple values

eg:

Department xxxxx   asbc     closed

Department  yyyyyyy             opened

Department  zzzzz qwer tyug     Reopen

I need to get the string between  'Department'  and 'Closed', 'Department and 'opened', 'Department' and 'Reopen'  into single field

Result:

New field

xxxxx   asbc

yyyyyyy

zzzzz qwer tyug

10 Replies
tomasz_tru
Valued Contributor

Re: sub String

Use mid function to cut first 10 and last 6 characters.

Tomasz

sasiparupudi1
Honored Contributor III

Re: sub String

Trim(TextBetween(Your Field,'Department ','closed'))

ychaitanya
Contributor III

Re: sub String

Try Something like this


FINAL:

Load CV,

Pick(WildMatch(CV,'*closed*','*opened*','*Reopen*'),

mid(CV,11,index(CV,'closed')-11),

mid(CV,11,index(CV,'opened')-11),

mid(CV,11,index(CV,'Reopen')-11)

)

as XXX

;

XZZ:

LOAD * INLINE

[CV

Department xxxxx   asbc     closed


Department  yyyyyyy             opened


Department  zzzzz qwer tyug     Reopen


];


Hope this helps


Thanks

CY

sasiparupudi1
Honored Contributor III

Re: sub String

Apologies, didnt read the question properly.. May be try

Pick(

Match([Your Field],'closed,'opened','Reopen'),

Trim(TextBetween([Your Field],'Department ','closed')),

Trim(TextBetween([Your Field],'Department ','opened')),

Trim(TextBetween([Your Field],'Department ','Reopen'))

)



XZZ:


LOAD CV,

Pick(


WildMatch(CV,'*closed','*opened','*Reopen'),


Trim(TextBetween(CV,'Department','closed')),


Trim(TextBetween(CV,'Department','opened')),


Trim(TextBetween(CV,'Department','Reopen'))


) As CV1


INLINE


[CV

Department xxxxx   asbc     closed

Department  yyyyyyy             opened

Department  zzzzz qwer tyug     Reopen

];

ychaitanya
Contributor III

Re: sub String

have you tried the above approaches ?

sasiparupudi1
Honored Contributor III

Re: sub String

Try this

XZZ:


LOAD CV,

WildMatch(CV,'*closed*','*opened*','*Reopen','*Opens*') as xxx,

Pick(


WildMatch(CV,'*closed*','*opened*','*Reopen*','*Opens*'),


Trim(TextBetween(CV,'Department','closed')),

Trim(TextBetween(CV,'Department','opened')),

Trim(TextBetween(CV,'Department','Reopen')),

Trim(TextBetween(CV,'Department','Opens'))


) As CV1


INLINE


[CV

Department xxxxx   asbc     closed

Department  yyyyyyy             opened

Department  zzzzz qwer tyug     Reopen

Department  AAAAAAAAAAAAAAAAA   closed today

Department  asd  cjffd ert qh  Opens every monday to friday

];

ychaitanya
Contributor III

Re: sub String

I have tried in my QV and it has worked as expected

FINAL:

Load CV,

Pick(WildMatch(CV,'*closed*','*opened*','*Reopen*'),

mid(CV,11,index(CV,'closed')-11),

mid(CV,11,index(CV,'opened')-11),

mid(CV,11,index(CV,'Reopen')-11)

)

as NEW_FIELD;

XZZ:

LOAD * INLINE

[CV

Department xxxxx   asbc     closed


Department  yyyyyyy             opened


Department  zzzzz qwer tyug     Reopen


];

Re: sub String

Hi Prithvi,

Script:


Data:

Load *, Trim(TextBetween(CV,'Department',SubField(CV,' ',-1))) as NewField;

LOAD * INLINE [

    CV

    Department xxxxx asbc closed

    Department yyyyyyy  opened

    Department zzzzz qwer tyug Reopen

];

If your department is dynamic data then try

Data:

Load *, Trim(TextBetween(CV,SubField(CV,' ',1),SubField(CV,' ',-1))) as NewField;

LOAD * INLINE [

    CV

    Department xxxxx asbc closed

    Department yyyyyyy  opened

    Department zzzzz qwer tyug Reopen

];

Result:

Capture.PNG

Note: This solution is purely based on your above data. If you have more scenarios then you have to post more examples.

Re: sub String

another possible solution can be

mid(Trim(CV),11,len(Trim(CV))-17)

Regards,

Prashant Sangle

Regards,
Prashant Sangle