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?

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?

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.

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

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?

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?