mapping load neglecting space

[Delphines]

hi everyone, 

I have this mapping load: 

SEARCH_FIELD:
mapping LOAD* INLINE [
SEARCH , REPLACE
' I3','| I3'
' I2','| I2'
' I1','| I1'
' I4','| I4'
' I5','| I5']

as I want to replace these strings in the 1st column by the ones in the 2nd column using a MapSubString function as below: 

TABLE:
LOAD MapSubString('SEARCH_FIELD',SEARCH) AS RESULT FROM MyData;

As you can see, there is a space at the beginning of the SEARCH elements ([space]I3 for instance) because I want the text to be replaced only if there is indeed a space before the "I". If there is no space, there shouldn't be a mapping. 

However, the MapSubString (or the mapping load) is not considering that space, which leads me to substitute the data where no substitution should be done. 

Do you have an idea on how I could fix this? 

thanks a lot for your help!

[Or]

I think you might be looking at the wrong culprit here.

If you just do a regular inline load of the information in your post, you'll note that the space isn't actually loaded. This doesn't seem to have anything to do with the fact that it's a mapping load.

If that's the case, you can work around it by concatenating a space:

Load ' ' & SEARCH as SEARCH, REPLACE as REPLACE inline [

Stuff here

];

[maxgro]

maybe

SEARCH_FIELD:
mapping LOAD CHR(32) & SEARCH as SEARCH, REPLACE INLINE [
SEARCH , REPLACE
'I3','| I3'
'I2','| I2'
'I1','| I1'
'I4','| I4'
'I5','| I5'
]
;

[rwunderlich · Accepted solution]

By default, leading and trailing blanks are stripped before the value is stored. You can override this behavior by setting the system variable Verbatim:

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/SystemVariab...

SET Verbatim = 1;
SEARCH_FIELD:
mapping LOAD* INLINE [
SEARCH , REPLACE
' I3','| I3'
' I2','| I2'
' I1','| I1'
' I4','| I4'
' I5','| I5']

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Delphines · Accepted solution]

many thanks @rwunderlich 

I did read this Verbatim stuff before on other threads but didn't understand how it works, but the output I have is the one I wished to have, so that the most important thing. But just for curiosity, if I put 2, does this mean it let 2 spaces if my field begin with 2 spaces ?

[rwunderlich]

No, it is a switch, 0 or 1. 

Setting this variable to 1 suspends the stripping of blanks. Setting to 0 resumes the default behavior of stripping blanks. 

-Rob

[EnriqueMartinez]

Verbatim didn't work for me. To fix words Capitalize can't hanlde well, I used this instead:

	preposis:
    Mapping
    Load
    	' ' & de & ' '	as de,
        ' ' & a & ' '	as a
    ;
    Load * Inline [
    de,		a
    En Pnl,	en PNL
    En Abc,	en ABC
    A La,	a la
    Para La, para la
    De La,	de la
    De Las,	de las
    De Los, de los
    Y Las,	y las
    Y La,	y la
    Y De,	y de
    En La,	en la
    La,		la
    Y,		y
    De,		de
    Del,	del
    A,		a
    En,		en
    E,		e
    Para,	para
    O,		o
    Al,		al
    Su,		su
    De Su,	de su
    Como,	como
    ];

[EnriqueMartinez]

Revisited version, with a Replace to help with words at the end, or followed by comma or similar.

	preposis:
    Mapping
    Load
    	Replace(de,'_',' ')	as de,
        Replace(a,'_',' ')	as a
    ;
    Load * Inline [
    de,		a
    Pnl,	PNL
    Abc,	ABC
    Iteso,	ITESO
    _A_La_,	_a_la_
    _Para_La_, _para_la_
    _De_La_,	_de_la_
    _De_Las_,	_de_las_
    _De_Los_, _de_los_
    _Y_Las_,	_y_las_
    _Y_La_,	_y_la_
    _Y_De_,	_y_de_
    _En_La_,	_en_la_
    _La_,		_la_
    _De_,		_de_
    _Y_,		_y_
    _Del_,	_del_
    _A_,		_a_
    _En_,		_en_
    _E_,		_e_
    _Para_,	_para_
    _O_,		_o_
    _Al_,		_al_
    _Su_,		_su_
    _De_Su_,	_de_su_
    _Como_,	_como_
    ];