topic Re: Truncating trailing 2 spaces from a field in QlikView

Truncating trailing 2 spaces from a field

sayadutt — Tue, 26 Aug 2014 06:52:09 GMT

HI,

i have a list where few values have 2 traling spaces while others don't.

how can I write a generalized code at the time of loading, which will delete the 2 trailing spaces (at end) if applicable else ignore.

Thanks

Re: Truncating trailing 2 spaces from a field

ashfaq_haseeb — Tue, 26 Aug 2014 07:04:03 GMT

HI,

use

Trim(field) as Field.

Regards

ASHFAQ

Re: Truncating trailing 2 spaces from a field

sayadutt — Tue, 26 Aug 2014 07:27:13 GMT

Hi Ashfaq,

How the Trim works when the Field consists of more than 1 word?

will

Trim([string1 string2]) as Field

work?

Thanks

Re: Truncating trailing 2 spaces from a field

ashfaq_haseeb — Tue, 26 Aug 2014 07:34:28 GMT

Hi,

Can you attach some sample data.

Regards

ASHFAQ

Re: Truncating trailing 2 spaces from a field

sayadutt — Tue, 26 Aug 2014 07:43:00 GMT

Associate Working On

Abdul Sheikh

Abhay Jagtap

Abhishek Gupta

Abhitanshu Shukla

Associate Working On : is the field name

And few records have 2 spaces at the end

Thanks

Re: Truncating trailing 2 spaces from a field

Tue, 26 Aug 2014 07:44:45 GMT

Hi ,

if u are merging two field then ,

Try Like this Trim(Field1)&Trim(Field2) as Field

Thanks Saurabh

Re: Truncating trailing 2 spaces from a field

tresB — Tue, 26 Aug 2014 07:46:09 GMT

Trim() removes the spaces from both ends and NOT from within the string. So you get your desired result with trim().

Re: Truncating trailing 2 spaces from a field

Tue, 26 Aug 2014 07:49:05 GMT

Hi , if u have spaces in right try like this Rtrim(Associate Working On) Thanks

Re: Re: Truncating trailing 2 spaces from a field

ashfaq_haseeb — Tue, 26 Aug 2014 07:51:26 GMT

Hi,

have a look at attached application.

use below code

load *, trim([Associate Working On]) as Test1,

Replace([Associate Working On],' ','') as Test2;

load * Inline

[

"Associate Working On"

"Abdul Sheikh "

"Abhay Jagtap"

"Abhishek Gupta"

"Abhitanshu Shukla "

];

Regards

ASHFAQ

Re: Truncating trailing 2 spaces from a field

sayadutt — Tue, 26 Aug 2014 08:58:02 GMT

Hi,

Your above sample works fine if we do a Inline Load.

But the moment I do load from SharePoint, 2 extra spaces are getting added at the end, and they are not getting truncated using Trim.

LOAD

Trim([Associate Working On]) as Test

FROM

[sharepoint list path]

(html, utf8, embedded labels, table is @4);

I am exporting Test to excel and can see 2 extra spaces added automatically. My data don't have these spaces.

Thanks

Re: Truncating trailing 2 spaces from a field

ashfaq_haseeb — Tue, 26 Aug 2014 09:01:35 GMT

Hi,

try with this and see.

Replace([Associate Working On],' ','') as Test2;

or you can store data in qvd and apply thees formulas while reading data from qvd.

Regards

ASHFAQ

Re: Truncating trailing 2 spaces from a field

tresB — Tue, 26 Aug 2014 09:02:49 GMT

Try:

set Verbatim = 0; // before your load statement.

Re: Truncating trailing 2 spaces from a field

MarcoWedel — Tue, 26 Aug 2014 09:07:11 GMT

Hi,

hope this helps

regards

Marco

Re: Truncating trailing 2 spaces from a field

jonathandienst — Tue, 26 Aug 2014 09:08:49 GMT

Sayantan Dutta wrote:

But the moment I do load from SharePoint, 2 extra spaces are getting added at the end, and they are not getting truncated using Trim.

Then they could be non-breaking spaces (unicode char 0xA0 (160)).

PurgeChar([Associate Working On], chr(160)) as Test

HTH

Jonathan

Re: Re: Truncating trailing 2 spaces from a field

MarcoWedel — Tue, 26 Aug 2014 09:18:25 GMT

Try

=Ord(Right([Associate Working On],1))

to get the ascii code of the character:

hope this helps

regards

Marco