ALT function not returning as expected?

[#1]

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?

[sunny_talwar]

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;

[#3]

Thanks for your suggestion!

2 follow-up questions:

1. Why is it that the ALT function cannot return 0 when no records meet the criteria?
2. 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?

Thanks!

[sunny_talwar]

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.

[maxgro]

1. 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;