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
Hi,
The
code which i have told you is used when you want to replace the null with some values.
Son in your code you can try this.
NullAsValue EVPM;
Set NullValue = '0' ;
SQL Select *
FROM ProjectManagement.dbo."PROJECT MANAGEMENT$Time Sheet Entry";
Regards,
Kaushik Solanki
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
Hi
For remove null values,
Try like this
If(Len(Trim(EVPM)) = 0, '000', EVPM) as EVPM
Hope that helps
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
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;
Hello Ralf,
i used Sql function isNULL() but it does not work
Hi IOSU,
I tried all functions, even this if(IsNull(EVPM), 0, EVPM) as NotNullEVPM, but nulls are still persisting
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"
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?
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