Weird MapSubString Behaviour. Wtf?

[pedrohenriqueperna]

Hi,

I have a mapping table that holds plenty values, but replacing numbers to the number name is not working properly for some reason I don't know why.

It seems to happen only in the following case (whatever number it is):

ReplaceNumber:

Mapping LOAD * Inline [

" 1 "," one "

" 2 "," two "

" 3 "," three "

" 4 "," four "

" 5 "," five "

" 6 "," six "

" 06 "," six "

];

Consider I have 2 rows in a table, one string the value is "NW 06" and the other is "NW 6". If I MapSubString, "NW 6" becomes "NW SIX", but "NW 06" remains the same. If I change the order within the table (06 first), the result is the opposite.

Why???

[Daniel_Pilla · Accepted solution]

Hi @pedrohenriqueperna ,

It is because the mapping load is converting `06` to the integer `6`. To preserve it as text, wrap it in the `Text()` function. The below works.

ReplaceNumber:
Mapping LOAD 
  	Text(Key) AS Key,
  	Value
Inline [
  Key	,Value
  1		,one
  2		,two
  3		,three
  4		,four
  5		,five
  6		,six
  06	,six
];

Data:
LOAD
	*,
    MapSubString('ReplaceNumber',Data) AS DataMapped
;
LOAD * INLINE [
	Data
    NW 6
    NW 06
];

[pedrohenriqueperna]

Oh, it makes sense

Thank you very much