Mapping Null values not working

[sajad_manzoor]

Hi,

I have following code:

//===========================================================================

TimeSheet:

SQL  SELECT

    "Entry No_",

    "Activity Code",

    "Consultant Code",

     EVPM,

    isNull("Man Days",0) as "Project ManDays",

     Month,

    "Working Date",

    "Project Code",

     Year

FROM ProjectManagement.dbo."PROJECT MANAGEMENT$Time Sheet Entry";

//=====================================================================================

ManDays:

SQL SELECT

    SUM("Man Days") as ManDays,

    Month,

    Year,

   "Consultant Code" 

FROM ProjectManagement.dbo."PROJECT MANAGEMENT$Time Sheet Entry"

Group By

      Month,

      Year,

     "Consultant Code";

//===============================================================================

Map_Nulls:

Mapping LOAD

Null(), '000' AutoGenerate ;

MAP EVPM USING Map_Nulls;

TimeSheet2:

NoConcatenate 

LOAD * RESIDENT TimeSheet;

DROP TABLE TimeSheet; 

I want to remove null values in EVPM,

But the above null mapping does not work at all,

please suggest 

[rbecher]

NLV is am Oracle function. This query more looks like SQL Server..

[sajad_manzoor]

This is the screen short , problem still not resolved:

[#4]

When loading, format EVPM as a number:

num(EVPM)

Maybe the problem is that it's loaded as a String...

[kaushiknsolanki]

Hi,

     You can try this.

     NullAsValue A,B;

     Set NullValue = 'NULL' ;

     Load A,B from x.csv;

     This setting will replace the null values to the values.

Regards,

Kaushik Solnaki

[sajad_manzoor]

the datatype of EVPM is decimal

[sajad_manzoor]

IOSU,

i changed my code to

TimeSheet:

LOAD

    "Entry No_",

    "Activity Code",

    "Consultant Code",

    IF(isNull(NUM( EVPM)),0,NUM(EVPM)) as EVPM,

    "Man Days" as "Project ManDays",

     Month,

    "Working Date",

    "Project Code",

     Year;

  SQL Select *

FROM ProjectManagement.dbo."PROJECT MANAGEMENT$Time Sheet Entry";

But i still have null values in EVPM

[sajad_manzoor]

Hello Kaushik,

If u can explain how to use NullAsValue with my code:

TimeSheet:

LOAD

    "Entry No_",

    "Activity Code",

    "Consultant Code",

    IF(isNull(NUM( EVPM)),0,NUM(EVPM)) as EVPM,

    "Man Days" as "Project ManDays",

     Month,

    "Working Date",

    "Project Code",

     Year;

  SQL Select *

FROM ProjectManagement.dbo."PROJECT MANAGEMENT$Time Sheet Entry";

[sajad_manzoor]

Ya , that is why i used isNULL() of sql server.

but all in vain ;-(

[rbecher]

Maybe, because of decimal use:

ISNULL(EVPM, 0.0) as "EVPM"

..or

COALESCE(EVPM, 0) as "EVPM"

[CELAMBARASAN]

I feel that problem happening your data is not because of null.

It looks like its in string format

try converting it to number format with this

IF(isNull(EVPM) or Len(Trim(EVPM)) = 0, 0, Num(Num#(EVPM, '#,###.##'))) as EVPM,

Hope this helps