topic Re: Data load editor, handle null values in mapping in App Development

Data load editor, handle null values in mapping

MarkH1 — Wed, 08 Dec 2021 16:12:48 GMT

Hi all,

In my data load editor script I use a mapping like this:

[activeMapping]:
MAPPING LOAD * INLINE
[
activeMapping-FROM,activeMapping-TO
1,Yes
0,No
];
[table1]:
LOAD
APPLYMAP( 'activeMapping', [active]) AS [active];
.
.

This works as expected but may data also contains NULL values or empty strings for the active field. How can I map these to (for instance) 'unknown' ?

TIA, Mark.

Re: Data load editor, handle null values in mapping

MarcoWedel — Wed, 08 Dec 2021 17:27:09 GMT

If there are no other possible [active] values than 1 and 0, then you can just add 'unknown' as third (default) parameter to the ApplyMap() function:

APPLYMAP( 'activeMapping', [active], 'unknown')

otherwise everything besides 1 and 0 would be mapped to 'unknown' as well, i.e. not only the NULL values.

But based on the field name active and values 1 and 0 most likely there are no other values anyways?

hope this helps

Marco

Re: Data load editor, handle null values in mapping

MarkH1 — Thu, 09 Dec 2021 12:33:35 GMT

Thanks Marco, that worked. In this case active can only be 0, 1 or undefined (null). I found out there is another option: in the load section do something like If(IsNull([active]), 'unknown', APPLYMAP( 'activeMapping', [active])) AS [active];
But your solution is cleaner.

Mark.

Re: Data load editor, handle null values in mapping

MarcoWedel — Thu, 09 Dec 2021 21:19:53 GMT

Glad it worked.

Please close your thread by accepting a solution if your question is answered.

thanks