Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Esneider
Partner
Partner

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

multiview
Creator II
Creator II

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

@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
Partner
Author

Excellent.

Thanks Rob Wunderlich.

rwunderlich

Please mark an answer as correct to close the thread.

-Rob