Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Raj,
Try this function : REPLACE(PartyName,' ','')
Let me know if this one help you.
Regards,
Sokkorn
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):
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
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.
thanks a lot.....
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
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