Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qvqfqlik
Creator
Creator

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
Specialist
Specialist

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

Tomasz

sasiparupudi1
Master III
Master III

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

ychaitanya
Creator III
Creator III

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
Master III
Master III

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
Creator III
Creator III

have you tried the above approaches ?

sasiparupudi1
Master III
Master III

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
Creator III
Creator III

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


];

tamilarasu
Champion
Champion

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.

PrashantSangle

another possible solution can be

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

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂