Ask Your Question

Larry's profile - activity

2013-11-06 22:50:22 +0200 received badge  Famous Question (source)
2013-10-30 17:24:16 +0200 received badge  Editor (source)
2013-10-30 17:21:28 +0200 answered a question How can I enter a conditional calculation in LO Base/Query

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

2013-10-30 16:15:01 +0200 commented answer How can I enter a conditional calculation in LO Base/Query

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 15:38:06 +0200 received badge  Notable Question (source)
2013-10-30 14:40:34 +0200 commented answer How can I enter a conditional calculation in LO Base/Query

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 08:23:33 +0200 received badge  Popular Question (source)
2013-10-30 03:35:00 +0200 commented answer How can I enter a conditional calculation in LO Base/Query

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?

2013-10-30 03:34:56 +0200 commented question How can I enter a conditional calculation in LO Base/Query

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-29 16:48:12 +0200 asked a question How can I enter a conditional calculation in LO Base/Query

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