# How can I enter a conditional calculation in LO Base/Query [closed]

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I would like to create a new column in my query that does a conditional calculation across several variables in the table used for the query. If it is easier or more direct, I could also do this in the table within the database.

A simple example of what I am trying to do would have a TestTable with the entries of: TableID, Primary Key, AutoValue Value1, Value2, and Value 3 all Integer (Long) Within the test table, there are 7 entries, with Value1, Value2 and Value3 ranging from 1 to 9 and if no entry was made, it shows up as a 0.

I would like to calculate the number of non-zero entries across the variables Value1, Value2 and Value3 for every case and enter that in a new column.

I tried to add a formula to the table, but haven't discovered how to do that, so I turned to Query and find that if I go in GUI I can add my test table and in Field I can simply type in Value1/Value1 and that becomes a new column with a value of 1 if the entry is something other than 0, or 0, if it was a 0. This seemed promising, for I could then create 2 more columns Value2/Value2 and Value3/Value3. Then I could Write in an Alias for each one and create 4th column Alias1+Alias2 + Alias3 and if all 3 values were non-zero, then I would get a 3, but if any one was a 0, I would get a 0, so this didn't give me the values I wanted.

In investigating SQL options, it looked like the CASE option could do what I wanted. Something like SELECT TableID, Value1, Value2, Value3, N1 = CASE WHEN Value1>0 THEN 1 ELSE 0 END FROM TestTable;

This would have to be repeated for each value and then, if the 0's created this way were really 0's that could be part of another calculation, then N1+N2+N3 would yield the value I want. But I can't even get past a single SQL entry this way without the same Syntax error.

Any Idea on how to do this simple calculation?

Larry

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-12 15:40:18.713114

What about Alias1 * Alias2 * Alias3 in 4th column?

( 2013-10-29 17:04:24 +0200 )edit

Thanks for you suggestion. When I try this idea, if Value1 is 5, Value2 is 4 and Value3 is 0 (meaning not entered), then if Alias1 is 1, Alias2 is 1 and Alias3 is 0 and the result of multiplying them together is 0. What I want is number of valid entries, which would be 2 in this example. Any other ideas?

( 2013-10-30 03:34:56 +0200 )edit

Sort by » oldest newest most voted

Try this function - NVL

NVL( "Value1" / "Value1", 0 ) + NVL( "Value2" / "Value2", 0 ) + NVL( "Value3" / "Value3", 0 )

more

Thanks for this idea. It also results in the error "Undefined function NVL in expression. I can see where you are going with this - would there be a way that when the calculation of "Value1"/"Value1" is done that it does redefine the null value as a Zero instead of a Null Value? I'll do some more research in that direction - again thanks for your idea.

( 2013-10-30 14:40:34 +0200 )edit

Yes, that is what I had in mind. Any arithmetic operation with a NULL value will give the result NULL of the entire expression. In other databases function NVL can be called IFNULL or ISNULL

( 2013-10-30 14:52:12 +0200 )edit

If I use IFNULL, I get the error that it is an undefined function. If I use ISNULL, I get an error "Wrong number of arguments used with function in query expression ISNULL(Value1_01,0), whereValue1_01is the Alias I assigned to the division of Value1 by itself. I can use ISNULL with the original division and assignment of Alias, e.g. ISNULL(Value1/Value1, 0) AS Value1_01 and I get the same error. It looks like ISNULL is a valid function which is great. Also went to http://technet.microsoft.com/en-us/library/ms184325.aspx and it looks like the way I entered it is right. What other arguments might be needed? Any thoughts?

( 2013-10-30 16:15:01 +0200 )edit

I have discovered the answer to this question and it is in the use of IIF statement. As noted above, I have a table called TestFormula withe TestID, Value1, Value2 and Value3 as the fields. Creating the table in Query I write:

SELECT TestID, Value1, Value2, Value3, IIF(Value1 > 0, 1, 0) + IIF(Value2 > 0, 1, 0) + IIF(Value3 > 0, 1, 0) AS SUM FROM TestFormula;

The new field with the alias of SUM counts across the fields of Value1, Value2 and Value3 and yields the number of non-zero fields from 0 to 3.

This solution could also work if I tried to count non-null fields if I wrote IIF(Value1 IS NULL, 0, 1) etc. but I don't have to go through the division I was originally doing which yielded the null values in the first place.

Thanks to those who provided suggestions, they ultimately led me to what feels like a pretty simple answer. Larry

more

Hi, Here is a HSQL statement that might do for you....!

I am attaching a PDF to show you output of this HSQL ..... Hope it helps.

SELECT "ID", "Val_1", "Val_2", "Val_3", "Val_1" + "Val_2" + "Val_3" AS "RESULT", CASEWHEN( ( "Val_1" + "Val_2" + "Val_3" ) > 0, 'Yes', 'No' ) AS "Logic_YesOrNo" FROM "TableName"

SORRY DUE TO LOW KARMA, I COULDN'T UPLOAD FILE....SORRY!

more

Thanks for this idea. When I enter this HSQL statement, I get the error "Undefined function CASEWHEN` in expression." I tried CASE WHEN and =CASE WHEN and =CASEWHEN and they get syntax errors. The Value of "Val_1"+"Val_2"+"Val_3" yields a correct sum, but when these values are 4,3 and 0, the result is 7. What I want is to count the number of valid entries where 4 and 3 are valid entries and 0 is not, so the result I want is the number 2. Any other ideas?