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 

[#2]

Hi,

On the first Load statement, you can just put this to replace null values with 0:

LOAD *,

     if(IsNull(EVPM), 0, EVPM) as NotNullEVPM

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

Regards

[MayilVahanan]

Hi

For remove null values,

Try like this

If(Len(Trim(EVPM)) = 0, '000', EVPM) as EVPM

Hope that helps

[rbecher]

Hi Sajad,

you could also use SQL ISNULL() function:

SQL  SELECT

    "Entry No_",

    "Activity Code",

    "Consultant Code",

     ISNULL(EVPM, 0) as "EVPM",

     ...

http://technet.microsoft.com/en-us/library/ms184325.aspx

- Ralf

[sajad_manzoor]

For Mapping Null values in EVPM , i used this code:

Map_Nulls:

Mapping LOAD

Null(), '000' AutoGenerate 1 ;

MAP EVPM USING Map_Nulls;

TimeSheet2:

NoConcatenate

LOAD * RESIDENT TimeSheet;

DROP TABLE TimeSheet;

[sajad_manzoor]

Hello Ralf,

i used Sql function isNULL() but it does not work

[sajad_manzoor]

Hi IOSU,

I tried all functions, even this if(IsNull(EVPM), 0, EVPM) as NotNullEVPM, but nulls are still persisting

[rbecher]

It depends on the data type of EVPM, which I don't know. It has to be the same type. If it's varchar you have to use:

ISNULL(EVPM, '0') as "EVPM"

..or

ISNULL(EVPM, '000') as "EVPM"

[#9]

So, maybe your problem comes up when you join one table to another... Could you post which table concatenations you are doing in the code?

[kaushiknsolanki]

Hi,

     When you are fetching the data from database and if you are doing any transaformation of data in SQL query then you should use the SQL functions instead of QlikView.

     In your case you should use the NVL function.

TimeSheet:

SQL  SELECT

    "Entry No_",

    "Activity Code",

    "Consultant Code",

     EVPM,

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

     Month,

    "Working Date",

    "Project Code",

     Year

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

NVL is a SQL command which replaces the NULL values to the value specified in function.

Hope this will solve your problem.

Regards,

Kaushik Solanki