Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try something like this:
trim(subfield(subfield(Text, '-', 2),'(', 1))
- Marcus
Hi,
Try:
TextBetween(VALUES,'- ',' (') | as "VALUES" |
Use the textbetween function like this
Load TextBetween(Text,'- ','(') as NewText;
Load * Inline [Text
'11 (123456) - Automotive (ABI)'
'AX112(123456) - British Airways (BAY)'
];
Hi,
Try this
LOAD
*,
Trim(Replace(TextBetween(FieldName, ')', '('), '-', '')) AS NewFieldName
FROM Datasource;
Regards,
Jagan.
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'
Hi
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"
You need to place these functionality within a preceeding load, see here what is meant: Preceding Load.
- Marcus
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;