Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
NLV is am Oracle function. This query more looks like SQL Server..
This is the screen short , problem still not resolved:
When loading, format EVPM as a number:
num(EVPM)
Maybe the problem is that it's loaded as a String...
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
the datatype of EVPM is decimal
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
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";
Ya , that is why i used isNULL() of sql server.
but all in vain ;-(
Maybe, because of decimal use:
ISNULL(EVPM, 0.0) as "EVPM"
..or
COALESCE(EVPM, 0) as "EVPM"
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