Cannot handle null values

[nskatzis]

Hello,

I have the following Load statement

rootValue.Current_Phase:
Load ProjectId_New as projectsId
Resident rootValueNew
where projectFieldId_New = 1;
left join
Load projectsId, [Current Phase]
Resident "rootValue.Current Phase";

It produces the attached result set:

I want to change the Null value to something else.

I replaced [Current Phase] in my script with all the possible combinations I could think of, such as:

if (IsNull([Current Phase] or len(trim([Current Phase]))< 2 or [Current Phase]='NULL' or [Current Phase]='' or [Current Phase]='-'), '0. No Phase', [Current Phase]) as [Current_Phase]

or the opposite
if (NOT IsNull([Current Phase] or len(trim([Current Phase]))<> 0), [Current Phase], '0. No Phase') as [Current_Phase]

But nothing.

Current_Phase is shown as '-'

I even displayed len(trim([Current Phase])). It is shown as '-'

Can you help?

Thank you,

Nikos

[BrunPierre]

Hi,

With NullAsValue statement as below.

NullAsValue [Current Phase] ;
Set NullValue = 'No Phase';

//----- Here's where your load statement goes -----\\
LOAD ...

[nskatzis]

Hello,

Thank you for your reply but

Unfortunately it didn't work.

This is my script:

NullAsValue [Current Phase];
Set NullValue = '0. No Phase';
NoConcatenate

// The below is done in order to include projects with No 'Current Phase -- N.S
rootValue.Current_Phase:

Load ProjectId_New as projectsId /*, Value_New as Value_New2 */
Resident rootValueNew
where projectFieldId_New = 1;
left join
Load projectsId, [Current Phase]
Resident "rootValue.Current Phase";

[BrunPierre · Accepted solution]

Instead of joining in this way, use mapping.

MapCurrentPhase:
Mapping Load projectsId,
[Current Phase]
Resident "rootValue.Current Phase";

Load ProjectId_New as projectsId, 
ApplyMap('MapCurrentPhase',ProjectId_New,'0. No Phase') as [Current Phase]
Resident rootValueNew;

[nskatzis]

That worked!!!

Thank you very much!

Nikos