topic Re: Weird MapSubString Behaviour. Wtf? in Connectivity & Data Prep

Weird MapSubString Behaviour. Wtf?

pedrohenriqueperna — Wed, 10 Jan 2024 18:01:13 GMT

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???

Re: Weird MapSubString Behaviour. Wtf?

Daniel_Pilla — Wed, 10 Jan 2024 18:29:05 GMT

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

Re: Weird MapSubString Behaviour. Wtf?

pedrohenriqueperna — Wed, 10 Jan 2024 18:40:26 GMT

Oh, it makes sense

Thank you very much