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: 
Esneider
Partner - Contributor III
Partner - Contributor III

Delete Space in Data

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?

14 Replies
Not applicable

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

Anonymous
Not applicable

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
 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

@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

Esneider
Partner - Contributor III
Partner - Contributor III
Author

Excellent.

Thanks Rob Wunderlich.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Please mark an answer as correct to close the thread.

-Rob