topic Re: trim function not working properly in my application in QlikView

trim function not working properly in my application

rajtechnocraft — Fri, 16 Sep 2011 06:22:10 GMT

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

Re: trim function not working properly in my application

Sokkorn — Fri, 16 Sep 2011 06:44:18 GMT

Hi Raj,

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

Let me know if this one help you.

Regards,

Sokkorn

Re: trim function not working properly in my application

gandalfgray — Fri, 16 Sep 2011 07:58:44 GMT

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

Re: trim function not working properly in my application

Fri, 16 Sep 2011 08:18:32 GMT

Hi,

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.

trim function not working properly in my application

rajtechnocraft — Fri, 16 Sep 2011 10:32:03 GMT

thanks a lot.....

Re: trim function not working properly in my application

Fri, 16 Sep 2011 11:01:13 GMT

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

trim function not working properly in my application

Fri, 16 Sep 2011 11:52:36 GMT

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