Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I wanted to call the sum(revenue) from a resident table depending on a few criteria, and I want to display 0 if no criteria is met.
TotalRevenue:
Load
alt(sum(revenue),0) as total_revenue
resident Revenue
where Is_Account_Active = 'Active'
;
My observation: TotalRevenue does not have any rows. If "Where" is removed, then total_revenue returns value as expected.
Question: How can I ensure that even if no records are found using the "Where" statement, I would still get 1 row for total_revenue with 0 as the value?
May be one of these based on the requirement
LOAD If(Is_Account_Active <> 'Active', 0, Alt(Sum(revenue), 0)) as total_revenue
Resident Revenue
Group By Is_Account_Active;
or
LOAD Alt(Sum(If(Is_Account_Active = 'Active', revenue)), 0) as total_revenue
Resident Revenue;
Thanks for your suggestion!
2 follow-up questions:
Thanks!
Hey John
1) Alt checks for null, but as explained below where just completely pushes the data out of the table so ALT cannot check for something which doesn't exist.
2) When you restrict something in Where clause, it never makes it to the table. So you cannot use conditions to be included in the table via Where clause. You have to use if or pick with Match/WilMatch/MixMatch to get it in the table but exclude from the calculation.
Does it make sense? I feel it is hard to explain, may be somebody else can have a better explanation for you.
- If i have multiple where conditions as opposed to "Account_Active='Active'", is there another way to do this without listing every condition into the if statement?
you can add a row, when there aren't rows
load 0 as total_revenue
AutoGenerate 1
Where NoOfRows('TotalRevenue') = 0;