topic Re: ALT function not returning as expected? in QlikView

ALT function not returning as expected?

Wed, 10 Feb 2016 16:56:44 GMT

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?

Re: ALT function not returning as expected?

sunny_talwar — Wed, 10 Feb 2016 17:35:29 GMT

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;

LOAD Alt(Sum(If(Is_Account_Active = 'Active', revenue)), 0) as total_revenue

Resident Revenue;

Re: ALT function not returning as expected?

Wed, 10 Feb 2016 17:44:54 GMT

Thanks for your suggestion!

2 follow-up questions:

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

Re: ALT function not returning as expected?

sunny_talwar — Wed, 10 Feb 2016 18:15:03 GMT

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.

Re: ALT function not returning as expected?

maxgro — Wed, 10 Feb 2016 21:57:35 GMT

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;