Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am using IsNull function in my script.
I have columns called Region, SalesRep, Sales.
LOAD * INLINE [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
NoConcatenate
Load *,
If(IsNull(Sales),Sales,0) as NewSales
Resident Details;
Drop table Details;
Question: For the SalesRep Madhu, the sale is Null.
Isnull Function returning "0" in the column "NewSales" for madhu. But, it is not showing the sales values where the actual sales is happend.
How to get the Actual sales as well as 0 in the "NewSales" field.
Regards
Srinivas
Hi,
other solutions without temporary tables, resident loads, IsNull() and string operations could be:
tabDetails:
LOAD Name,
SalesRep,
RangeSum(Sales,0) as Sales
Inline [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
or
tabDetails:
LOAD Name,
SalesRep,
Alt(Sales*1,0) as Sales
Inline [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
hope this helps
regards
Marco
Try using this way:
If(IsNull(Sales),0,Sales) as NewSales
The IF sintax is CONDITION, TRUE, FALSE. The way that you put it, every time that there's a null value on Sales, you will write it, and when it's not null, you will write 0.
Regards,
Gabriel
Try this instead:
NoConcatenate
Load *,
If(Len(Trim(Sales)) > 0, Sales, 0) as NewSales
Resident Details;
2 problems:
// 1) you have a blank or space in the Sales field for Madhu, not a null; if you want a null you can add the bold
Details:
LOAD Name, SalesRep, if(len(trim(Sales))=0, null(), Sales) as Sales INLINE [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
// 2) now you have a true null, you can check for null, replacing null with 0
Final:
NoConcatenate
Load
*,
If(IsNull(Sales),0,Sales) as NewSales
Resident Details;
Drop table Details;
Hi,
other solutions without temporary tables, resident loads, IsNull() and string operations could be:
tabDetails:
LOAD Name,
SalesRep,
RangeSum(Sales,0) as Sales
Inline [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
or
tabDetails:
LOAD Name,
SalesRep,
Alt(Sales*1,0) as Sales
Inline [
Name, SalesRep, Sales
HYD, sravan, 2000
Pune, Madhu
Delhi, Srinivas, 3000
Banglore, Bhuvan, 15000
Chennai, Karthik, 17000
];
hope this helps
regards
Marco
try this one If(IsNull(Sales),0,Sales) as NewSales
You could use the Alt() function, which is designed for this problem:
Alt(Sales, 0) As NewSales,
Use only Sales in place of IsNull(Sales)
If(Sales,Sales,0) as NewSales
And
go to Presentation Tab of Table
Unclick Suppress Zero-Values.
for SalesRep 'Madhu' Sales is not NULL value it is a missing value.
PFA QVW with requested Sales Data.