Ask Your Question
0

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

asked 2013-10-29 16:48:12 +0200

this post is marked as community wiki

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 flag offensive 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

Comments

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

JohnSUN gravatar imageJohnSUN ( 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?

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

3 Answers

Sort by » oldest newest most voted
0

answered 2013-10-30 01:47:21 +0200

MQ-818 gravatar image

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!

edit flag offensive delete link more

Comments

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?

Larry gravatar imageLarry ( 2013-10-30 03:35:00 +0200 )edit
0

answered 2013-10-30 08:21:33 +0200

JohnSUN gravatar image

updated 2013-10-30 08:25:39 +0200

Try this function - NVL

NVL( "Value1" / "Value1", 0 ) + NVL( "Value2" / "Value2", 0 ) + NVL( "Value3" / "Value3", 0 )
edit flag offensive delete link more

Comments

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.

Larry gravatar imageLarry ( 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

JohnSUN gravatar imageJohnSUN ( 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?

Larry gravatar imageLarry ( 2013-10-30 16:15:01 +0200 )edit
0

answered 2013-10-30 17:21:28 +0200

Larry gravatar image

updated 2013-10-30 17:24:16 +0200

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-10-29 16:48:12 +0200

Seen: 2,394 times

Last updated: Oct 30 '13