topic Extracting Word from a text String in App Development

Extracting Word from a text String

KevinJ — Fri, 15 Nov 2024 21:04:55 GMT

I have this data. I want to extract it to OPEX, CAPEX, GREENLANE and use it as a column. Is that possible?

it will be a measure or dimension? Is that Possible?

Initial Table:

Bacolod LBE OPEX

Cebu LBE GRN LANE

Terminal Ops VISAYAS GRN LANE

Surigao LBE OPEX

Terminal Ops MINDANAO OPEX

Manila LBE GRN LANE

Bacolod LBE OPEX

Zamboanga LBE OPEX

Cebu LBE GRN LANE

Terminal Ops MINDANAO OPEX

RESULTS:

Bacolod LBE OPEX	OPEX
Cebu LBE GRN LANE	GRN LANE
Terminal Ops VISAYAS GRN LANE	GRN LANE
Surigao LBE OPEX	OPEX
Terminal Ops MINDANAO OPEX	OPEX
Manila LBE GRN LANE	GRN LANE
Manila LBE GRN LANE	GRN LANE
Bacolod LBE OPEX	OPEX
Zamboanga LBE OPEX	OPEX
Cebu LBE GRN LANE	GRN LANE
Terminal Ops MINDANAO OPEX	OPEX

Re: Extracting Word from a text String

LRuCelver — Mon, 05 Feb 2024 14:29:42 GMT

If you only have the three words you need to extract and they always appear at the end of the texts, you can use a few nested IFs to create the 2nd column. This can be done as both a measure or a dimension.

If(Right(TEXTFIELD, 4) = 'OPEX', 'OPEX', If(Right(TEXTFIELD, 5) = 'CAPEX', 'CAPEX', If(Right(TEXTFIELD, 9) = 'GREENLANE', 'GREENLANE', '-' // Else. Delete this row or replace with Null() if this should be Null. )))

If you need a more dynamic solution, creating a dimension might be the easier solution as long as the words always appear at the end. If they don't, it'll be a lot more complicated.

Re: Extracting Word from a text String

marcus_sommer — Mon, 05 Feb 2024 14:38:27 GMT

I suggest to do the job within the data-model, maybe with:

pick(wildmatch(MyField, '*OPEX*', '*CAPEX*', '*GRNLANE*'),
'OPEX', 'CAPEX', 'GREENLANE') as MyFieldExtract

Re: Extracting Word from a text String

Anil_Babu_Samineni — Tue, 06 Feb 2024 05:17:36 GMT

@KevinJ Try this way?

If(Right(FieldName, 4)='OPEX', 'OPEX', SubField(FieldName, ' ', -2) & ' ' & SubField(FieldName, ' ', -1))