topic Re: Remove Text between a brakets in QlikView

Remove Text between a brakets

NavinReddy — Thu, 22 Sep 2016 12:31:03 GMT

Hello Dear Experts,

Good Evening

i'm trying to remove the word mentioned in brackets.

i have tried which i'm not able to achieve can some one help me.

Actual Text:

% Month Review - % of accounts that qualified and receive a deprice (KDC3304 +Dynamic Tier+) %

$ Debit Card Gross Fraud Rate (BPS) (KXO2203 +Dynamic Tier+) #

$ Debit Fraud Detection Rate (%) (KXB10235) %

# Accounts charged-off outside policy timelines (KXO1410) #

Expecting Result:

% Month Review - % of accounts that qualified and receive a deprice %

$ Debit Card Gross Fraud Rate (BPS) #

$ Debit Fraud Detection Rate (%) %

# Accounts charged-off outside policy timelines #

please help me to resolve this issue

Thanks in Advance

Many Thanks,

Niranjan

Re: Remove Text between a brakets

sunny_talwar — Thu, 22 Sep 2016 12:35:20 GMT

Is this one data row or is this four rows of data?

Re: Remove Text between a brakets

adamdavi3s — Thu, 22 Sep 2016 12:50:36 GMT

I think any dynamic formula will struggle as you also have text inside brackets that you want to keep, but I am interested to see what Sunny comes up with as always 😉

Re: Remove Text between a brakets

sunny_talwar — Thu, 22 Sep 2016 12:51:48 GMT

Well, first things first we need to know that is this 4 different rows of data or single row with Chr(10)s or Chr(13)s

Re: Remove Text between a brakets

sunny_talwar — Thu, 22 Sep 2016 12:56:27 GMT

For 4 rows, may be this:

Table:

LOAD Field,

Left(Field, Index(Field, '(', -1)-1) & Right(Field, Len(Field) - Index(Field, ')', -1) -1) as NewField;

LOAD * INLINE [

Field

% Month Review - % of accounts that qualified and receive a deprice (KDC3304 +Dynamic Tier+) %

$ Debit Card Gross Fraud Rate (BPS) (KXO2203 +Dynamic Tier+) #

$ Debit Fraud Detection Rate (%) (KXB10235) %

# Accounts charged-off outside policy timelines (KXO1410) #

];

Re: Remove Text between a brakets

sasiparupudi1 — Thu, 22 Sep 2016 13:22:53 GMT

Table:

LOAD T1,

SubStringCount( T1,')') as ind,

Replace(T1,

'('&

TextBetween(T1,'(',')',

SubStringCount( T1,')'))

&')'

,'') as T2;

LOAD * INLINE [

% Month Review - % of accounts that qualified and receive a deprice (KDC3304 +Dynamic Tier+) %

$ Debit Card Gross Fraud Rate (BPS) (KXO2203 +Dynamic Tier+) #

$ Debit Fraud Detection Rate (%) (KXB10235) %

# Accounts charged-off outside policy timelines (KXO1410) #

];

Re: Remove Text between a brakets

NavinReddy — Thu, 22 Sep 2016 13:26:46 GMT

Thank you very much Sunny, its working fine

Thanks again

Re: Remove Text between a brakets

NavinReddy — Thu, 22 Sep 2016 13:30:16 GMT

Thank you Sasidhar,its working fine

Re: Remove Text between a brakets

antoniotiman — Thu, 22 Sep 2016 13:35:10 GMT

=Replace(Field, '('&SubField(Field,' (',-1),'')& Right(Field,1)

Re: Remove Text between a brakets

johnb023 — Mon, 20 Sep 2021 19:47:09 GMT

This is a solution I was looking for.

I found that in the case where there were no brackets in the string, the first letter of the string was dropped.

I got around this by checking for brackets first...

if(SubStringCount(Field, '(') > 0 and SubStringCount(Field, ')') > 0 ...

There may be a more elegant solution.