Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rajtechnocraft
Contributor III
Contributor III

trim function not working properly in my application

Hi community ,

I am facing difficulty in trim function

I have PartyColumn in my Pivot Report

PartyName:

Franklin    Investments   ABC

I used trim function

trim(PartyName)

But still i am getting same output with spaces between names.

I also tried using ltrim,rtrim but am getting same output

On the contrary if i used the same function in excel trim.It is working

Can anyone help me.

Required this on urgent basi.

As the column PartyName is having 4000 records

So it is becoming extremely difficult for me

Though temporary i have done it in excel and created a table and then reimported it into my qvw but wantedto know why QV can't do it if excel can do it.

Regards

Raj

6 Replies
Sokkorn
Master
Master

Hi Raj,

Try this function : REPLACE(PartyName,' ','')

Let me know if this one help you.

Regards,

Sokkorn

gandalfgray
Specialist II
Specialist II

Hi Raj

QlikViews trim function does not do what you want to do because it's not designed that way.

From the QV help (F1):

trim(s )

Returns the string s trimmed of any leading and trailing spaces.

And I don't think Sokkorns suggestion gives what you want beacuse REPLACE(PartyName,' ','') will completetly remove all spaces in PartyName.

However I think you could use REPLACE(PartyName,'  ',' ') (that is replacing two spaces with one space) in a loop of some kind (as long as there are PartyNames = '*  *')

/gg

Not applicable

Hi,

in

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

are explained how to use regular expressions.

With the reugular expression

" {2,}"            ( space{2,} )

can you search all places with two or more spaces. Replace it with RegExReplace with one space. Finally a trim for leading and tailing space and that`s it.

rajtechnocraft
Contributor III
Contributor III
Author

thanks a lot.....

Not applicable

Hi,

You can use subfield

say party name is xx      yy   zzz

if you write subfield(party, ' ', 1) & ' ' & subfield(partym ' ', 2) as par

then you get xx yy

but more no of subfields may be required.

Reg,

Shubhu

Not applicable

If you don't want to use external functions you can either you brake it down and rebuild like shubhu007 suggested or you can do a "fast fix". The fix could look something like this:

replace(replace(replace(replace(replace(replace(replace(replace(trim(PartyName),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ') as PartyName

This example should reduce up to 256 spaces (2 ^ 😎 in a row to a single space. If you have more spaces in a row just add a few more replace() and you should be fine.

//Nils