topic Re: mapping load neglecting space in QlikView

mapping load neglecting space

Delphines — Tue, 05 Sep 2023 15:59:29 GMT

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!

Re: mapping load neglecting space

Or — Tue, 05 Sep 2023 16:20:04 GMT

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

];

Re: mapping load neglecting space

maxgro — Tue, 05 Sep 2023 16:44:09 GMT

maybe

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

Re: mapping load neglecting space

rwunderlich — Tue, 05 Sep 2023 17:13:30 GMT

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/SystemVariables/Verbatim.htm

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

Re: mapping load neglecting space

Delphines — Fri, 08 Sep 2023 17:22:20 GMT

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 ?

Re: mapping load neglecting space

rwunderlich — Sat, 09 Sep 2023 23:52:32 GMT

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

Re: mapping load neglecting space

EnriqueMartinez — Sun, 27 Oct 2024 02:10:49 GMT

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 ];

Re: mapping load neglecting space

EnriqueMartinez — Sun, 27 Oct 2024 02:09:12 GMT

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_ ];