topic Re: Cannot handle null values in App Development

Cannot handle null values

nskatzis — Tue, 07 Mar 2023 11:00:48 GMT

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

Re: Cannot handle null values

BrunPierre — Tue, 07 Mar 2023 11:50:03 GMT

Hi,

With NullAsValue statement as below.

NullAsValue [Current Phase] ; Set NullValue = 'No Phase'; //----- Here's where your load statement goes -----\\ LOAD ...

Re: Cannot handle null values

nskatzis — Tue, 07 Mar 2023 12:24:28 GMT

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

Re: Cannot handle null values

BrunPierre — Tue, 07 Mar 2023 13:04:39 GMT

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;

Re: Cannot handle null values

nskatzis — Tue, 07 Mar 2023 13:52:51 GMT

That worked!!!

Thank you very much!

Nikos