topic Re: remove brackets from field in QlikView

remove brackets from field

Thu, 29 Jan 2015 17:42:34 GMT

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

Re: remove brackets from field

its_anandrjs — Thu, 29 Jan 2015 17:54:26 GMT

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

Re: remove brackets from field

Thu, 29 Jan 2015 18:02:15 GMT

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

Re: remove brackets from field

its_anandrjs — Thu, 29 Jan 2015 18:16:37 GMT

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

Re: remove brackets from field

Thu, 29 Jan 2015 18:44:43 GMT

works can you please explain what this line does

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

Chr(44) is comma

what does '&' do in this script.

Re: remove brackets from field

its_anandrjs — Thu, 29 Jan 2015 18:53:26 GMT

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