topic Re: Extract a part from String in QlikView

Extract a part from String

brijesh1991 — Sat, 16 Nov 2024 04:39:12 GMT

I have few strings like

"sulko uspedo (00:20)+podendo (01:05)+Uskudento (05:01)"

"podendo (07:25)+sulko uspedo droggen(05:50)+Uskudento (05:01)"

"podendo (04:85)+Uskudento (03:21)+sulko uspedo (02:15)"

I need to extract time after key word sulko*

Result of above 3 sample should be 00:20, 05:50, 02:15 respectively; any idea on extracting time after a specific keyword would be highly appreciated. Thank you!

Re: Extract a part from String

Gysbert_Wassenaar — Sat, 26 Jan 2019 17:11:19 GMT

Perhaps like this:

Example:
LOAD
    ExampleStrings,
    KeywordTimePart,
    SubField(KeywordTimePart, '(', 1) as Keyword,
    Time#(PurgeChar(SubField(KeywordTimePart, '(', 2),')'),'hh:mm') as Time
    ;
LOAD
    ExampleStrings,
        SubField(ExampleStrings, '+') as KeywordTimePart
    ;
LOAD * INLINE [
ExampleStrings
sulko uspedo (00:20)+podendo (01:05)+Uskudento (05:01)
podendo (07:25)+sulko uspedo droggen(05:50)+Uskudento (05:01)
podendo (04:85)+Uskudento (03:21)+sulko uspedo (02:15)
];

Re: Extract a part from String

MindaugasBacius — Sat, 26 Jan 2019 18:01:44 GMT

I would use TextBetween function like below:

LOAD *
	,TextBetween(Right(text, Len(text) - Index(text, 'sulko')), '(', ')') as Time
INLINE [
text
sulko uspedo (00:20)+podendo (01:05)+Uskudento (05:01)
podendo (07:25)+sulko uspedo droggen(05:50)+Uskudento (05:01)
podendo (04:85)+Uskudento (03:21)+sulko uspedo (02:15)
];

There is the results: