topic Re: Mapping Null values not working in QlikView

Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 08:25:38 GMT

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

Re: Mapping Null values not working

Tue, 27 Aug 2013 08:28:58 GMT

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

Re: Mapping Null values not working

MayilVahanan — Tue, 27 Aug 2013 08:30:44 GMT

For remove null values,

Try like this

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

Hope that helps

Re: Mapping Null values not working

rbecher — Tue, 27 Aug 2013 08:31:05 GMT

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 08:31:07 GMT

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;

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 08:32:30 GMT

Hello Ralf,

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 08:34:05 GMT

Hi IOSU,

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

Re: Mapping Null values not working

rbecher — Tue, 27 Aug 2013 08:36:50 GMT

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"

Re: Mapping Null values not working

Tue, 27 Aug 2013 08:38:13 GMT

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?

Re: Mapping Null values not working

kaushiknsolanki — Tue, 27 Aug 2013 08:40:50 GMT

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

Re: Mapping Null values not working

rbecher — Tue, 27 Aug 2013 08:42:23 GMT

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 08:45:26 GMT

This is the screen short , problem still not resolved:

Re: Mapping Null values not working

Tue, 27 Aug 2013 08:48:06 GMT

When loading, format EVPM as a number:

num(EVPM)

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

Re: Mapping Null values not working

kaushiknsolanki — Tue, 27 Aug 2013 08:59:09 GMT

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 09:04:49 GMT

the datatype of EVPM is decimal

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 09:13:44 GMT

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 09:16:09 GMT

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

Re: Mapping Null values not working

sajad_manzoor — Tue, 27 Aug 2013 09:24:35 GMT

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

but all in vain ;-(

Re: Mapping Null values not working

rbecher — Tue, 27 Aug 2013 09:33:14 GMT

Maybe, because of decimal use:

ISNULL(EVPM, 0.0) as "EVPM"

..or

COALESCE(EVPM, 0) as "EVPM"

Re: Mapping Null values not working

CELAMBARASAN — Tue, 27 Aug 2013 09:34:22 GMT

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