remove brackets from field

[#1]

I have a field that is being pulled from database that i want to alter in qlikview.

as pulled from DB =     Martha (EL)

how i want it in qlikview as as =  Martha, EL

How can i accomplish that.

thanks

[its_anandrjs]

Hi,

You can try this way to load the table and use Purgechar to remove the brackets and then use chr(44) for comma

Ex:-

Your correct script is this

LOAD
Replace(PurgeChar(TextCol,'()'),' ',Chr(44)&' ') as NewText,
SubField(TextCol,'(') as TT,
*;
LOAD * Inline
[
TextCol
Martha (EL)
];

Regards
Anand

[#3]

Thanks Anand

Before you posted i tried below in text box and found the solution but require further help.

=replace(PurgeChar(fieldname,'()'),' ',',')

The problem with above code is that it is replacing every single space with comma in results. However i only want last space to be removed.

for example

Martha Stewart (MS)  ==  Martha Stewart, MS

Bruce Carl Senior (TT)  ==  Bruce Carl Senior, TT

[its_anandrjs · Accepted solution]

Then try this script

LOAD

PurgeChar( Replace(TextCol,' '&'(',Chr(44)&' '),')') as NewText,

*;
LOAD * Inline
[
TextCol
Martha (EL)
Martha Stewart (MS)
Bruce Carl Senior (TT)
];

Regards

Anand

[#5]

works can you please explain what this line does

Replace(TextCol,' '&'(',Chr(44)&' ')

Chr(44) is comma

what does '&' do in this script.

[its_anandrjs]

Hi,

By expression

Replace(TextCol,' '&'(',Chr(44)&' ')

1. In row this finds space ' ' with open bracket '(' here & is concat keyword that make sinlge string that is =  ' '&'('

2. then this replace ' '&'(' to  Chr(44)' '  that is Chr(44)&' '  here Chr(44) means comma.

LOAD
PurgeChar( Replace(TextCol,' '&'(',Chr(44)&' '),')') as NewText,
Replace(TextCol,' '&'(',Chr(44)&' ') as Step1,
PurgeChar( Replace(TextCol,' '&'(',Chr(44)&' '),')') as Step2,
*;
LOAD * Inline
[
TextCol
Martha (EL)
Martha Stewart (MS)
Bruce Carl Senior (TT)
];

Regards

Anand