Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use mid function to cut first 10 and last 6 characters.
Tomasz
Trim(TextBetween(Your Field,'Department ','closed'))
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
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
];
have you tried the above approaches ?
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
];
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
];
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:
Note: This solution is purely based on your above data. If you have more scenarios then you have to post more examples.
another possible solution can be
mid(Trim(CV),11,len(Trim(CV))-17)
Regards,
Prashant Sangle