topic Re: Extract text between special characters in QlikView

Extract text between special characters

techvarun — Mon, 27 Apr 2015 09:58:06 GMT

Hi Qlikxperts,

I have values like below and I want to extract only the Text in between - and (

11 (123456) - Automotive (ABI)

AX112(123456) - British Airways (BAY)

I need Automotive for row1 and British Airways for row2

Thanks

Varun

Re: Extract text between special characters

marcus_sommer — Mon, 27 Apr 2015 10:03:35 GMT

Try something like this:

trim(subfield(subfield(Text, '-', 2),'(', 1))

- Marcus

Re: Extract text between special characters

fvelascog72 — Mon, 27 Apr 2015 10:04:31 GMT

Hi,

Try:

TextBetween(VALUES,'- ',' (')

as "VALUES"

Re: Extract text between special characters

stigchel — Mon, 27 Apr 2015 10:07:07 GMT

Use the textbetween function like this

Load TextBetween(Text,'- ','(') as NewText;

Load * Inline [Text

'11 (123456) - Automotive (ABI)'

'AX112(123456) - British Airways (BAY)'

];

Re: Extract text between special characters

jagan — Mon, 27 Apr 2015 10:08:18 GMT

Hi,

Try this

LOAD

Trim(Replace(TextBetween(FieldName, ')', '('), '-', '')) AS NewFieldName

FROM Datasource;

Regards,

Jagan.

Re: Extract text between special characters

awhitfield — Mon, 27 Apr 2015 10:17:41 GMT

Hi Techvarun

TextBetween(Your Filed ,'- ', '(') AS Newfield

Regards

Andy

TextBetween(s , beforetext , aftertext [, n ])

Returns the text between the n:th occurrence of beforetext and the immediately following occurrence of aftertext within the string s.

Examples:

TextBetween('<abc>', '<', '>') returns 'abc

>TextBetween('<abc><de>', '<', '>',2) returns 'de'

Re: Extract text between special characters

Tue, 05 Apr 2016 09:26:56 GMT

Is there a way of carrying this same function out when loading from SQL data base? I cant seem to get it working!

"ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'TextBetween' is not a recognized built-in function name.

SQL SELECT*,

Trim(Replace(TextBetween(MeetingDateTime, ')', '('), '-', '')) AS PkDate"

Re: Extract text between special characters

marcus_sommer — Tue, 05 Apr 2016 09:29:52 GMT

You need to place these functionality within a preceeding load, see here what is meant: Preceding Load.

- Marcus

Re: Extract text between special characters

Tue, 05 Apr 2016 11:51:33 GMT

TableName1:

SQL SELECT * FROM Table;

TableName2:

LOAD

trim(subfield(subfield(YourField, 'The-Start-Deliminator', 2[Subfield Number]),'The-End-Deliminator', 1)) //This is a QV function, not a SQL function.

RESIDENT TableName1;

DROP TABLE TableName1;