Data load editor, handle null values in mapping

[MarkH1]

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.

[MarcoWedel · Accepted solution]

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

[MarkH1]

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.

[MarcoWedel]

Glad it worked.

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

thanks