topic Re: Delete Space in Data in QlikView

Delete Space in Data

Esneider — Wed, 23 Jan 2013 06:23:46 GMT

Hi,

In one of my fields, I have data like ' XXXX YYY ZZ' and I want them as 'XXXX YYY ZZ'

How can I do that?

Re: Delete Space in Data

Wed, 23 Jan 2013 06:39:22 GMT

While loading your data in script editor, You can use ltrim() function to remove leading spaces.

example:

from xyz.......

similary rtrim() to remove spaces from the end.

Below will remove leading and trail spaces from column name Relation and alias name of that column will be rel

ltrim(rtrim(Relation)) as rel

LOAD

ltrim(Relation) as rel

Re: Delete Space in Data

Wed, 23 Jan 2013 06:39:50 GMT

While loading your data in script editor, You can use ltrim() function to remove leading spaces.

example:

LOAD

ltrim(Relation) as rel

from xyz.......

similary rtrim() to remove spaces from the end.

Below will remove leading and trail spaces from column name Relation and alias name of that column will be rel

ltrim(rtrim(Relation)) as rel

Re: Delete Space in Data

sujeetsingh — Wed, 23 Jan 2013 06:40:11 GMT

If the trend is constant then we can do it.

let the field be Name

expression can be like this

=Subfield(Name,' ')

may it work

Re: Delete Space in Data

sivarajs — Wed, 23 Jan 2013 06:49:04 GMT

This might helps you

Replace(LTrim(' XXXX YYY ZZ'),' ',' ')

Re: Delete Space in Data

rwunderlich — Wed, 23 Jan 2013 06:52:48 GMT

One solution would be to use a MAPPING TABLE wih MapSubstring().

SpaceMap:

MAPPING LOAD repeat(' ', RecNo()), ' '

AutoGenerate 10 // up to 10 spaces

;

And in your LOAD statement:

MapSubString('SpaceMap', trim(x)) as y

-Rob

Re: Delete Space in Data

Anonymous — Wed, 23 Jan 2013 09:29:27 GMT

That's an excellent solution!

Regards

MultiView

Re: Delete Space in Data

sujeetsingh — Wed, 23 Jan 2013 09:44:56 GMT

Rob ,

will you please explain how it works

Re: Delete Space in Data

Esneider — Wed, 23 Jan 2013 12:31:30 GMT

Hi Rob,

Please can you send a example in qvw?

Thanks,

Re: Delete Space in Data

Esneider — Wed, 23 Jan 2013 12:32:39 GMT

Hi,

You have the example in qvw?

Re: Delete Space in Data

Wed, 23 Jan 2013 12:41:07 GMT

Hi,

Use functions to remove spaces: Replace () and LTrim ().

LTrim() : Returns the string trimmed of any leading spaces.

Use :

Replace(LTrim(' XXXX YYY ZZ'),' ',' ')

Regards,

Snehal Nabar

Re: Delete Space in Data

Anonymous — Wed, 23 Jan 2013 12:44:24 GMT

This is an example of Rob Wunderlich's technique, check CLEANED_TEXT after cleaning from spaces.

SpaceMap:

MAPPING LOAD repeat(' ', RecNo()), ' '
AutoGenerate 10 // up to 10 spaces
;

SAMPLE:
LOAD * Inline
[TEXT
HELLO AND WELCOME
THIS IS A SAMPLE TEST
I AM NOT SURE
];

DATA2:
LOAD MapSubString('SpaceMap', trim(TEXT)) as CLEANED_TEXT
Resident SAMPLE;

I hope this helps!

MultiView

Re: Delete Space in Data

rwunderlich — Wed, 23 Jan 2013 15:37:56 GMT

@MultiView, thanks for posting the excellent example!

@Son of Sardar, The MAPPING LOAD generates a standard two column mapping table that maps n blanks to one blank. It uses the repeat() function to generate 1 blank on the first row, 2 blanks on the second row and so on -- up to 10 blanks (AutoGenerate 10). The MapSubstring() function uses that mapping table to translate groups of blanks in the text to a single blank.

-Rob

Re: Delete Space in Data

Esneider — Wed, 23 Jan 2013 17:36:45 GMT

Excellent.

Thanks Rob Wunderlich.

Re: Delete Space in Data

rwunderlich — Thu, 24 Jan 2013 07:43:21 GMT

Please mark an answer as correct to close the thread.

-Rob