OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Calculated Fields and If-Then Else

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
kcheek
Newbie
Newbie


Joined: 21 Feb 2009
Posts: 2

PostPosted: Sat Feb 21, 2009 4:58 pm    Post subject: [SOLVED] Calculated Fields and If-Then Else Reply with quote

I've set up a weather database in the HSQL engine to keep up with the daily maximum temperature, minimum temperature, and precipitation. I intended to use calculated fields in a query to determine the daily mean temperature, Heating Degree Days and Cooling Degree Days. I made a calculated field with the alias "MeanTemp" with the following formula: ROUND(("MaxTemp" + "MinTemp")/2,1), which works perfectly. However, I can't find an equivalent to the MS Access IF() function to calculate Heating and Cooling Degree Days.

The logic for Heating Degree Days is as follows:

IF MeanTemp < 55 THEN 55 - MeanTemp ELSE 0

In MS Access I would write the expression as:

iif( MeanTemp < 55, 55 - MeanTemp, 0)

I tried using

CASE WHERE "MeanTemp" < 55 THEN 55 - "MeanTemp" ELSE 0

But in Design View I get an error that the name is unrecognized.

Any suggestions? I'm sure there's a simple answer.

Also, is there an equivalent for an Average function in a calculated field? I tried using AVERAGE("MaxTemp","MinTemp") and AVERAGE("MaxTemp;"MinTemp"), but neither worked.

Thanks in advance.


Last edited by kcheek on Sun Feb 22, 2009 3:19 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Feb 21, 2009 5:07 pm    Post subject: Reply with quote

kcheek:

Welcome to the world of OpenOffice Base.

You asked:
kcheek wrote:
In MS Access I would write the expression as:

iif( MeanTemp < 55, 55 - MeanTemp, 0)


The answer is, replace iif above with CASEWHEN ( one word, no spaces ).

According to HSQL documentation found at: http://www.hsqldb.org/doc/guide/ch09.html#N1251E

HSQL Documenation: http://www.hsqldb.org/doc/guide/ch09.html#N1251E wrote:

CASEWHEN(exp,v1,v2)

if exp is true, v1 is returned, else v2)


So, in your case, on the Field line, it would probably be ( I would also suggest adding an Alias Name ):

Code:
CASEWHEN(ROUND(("MaxTemp" + "MinTemp")/2,1) < 55, 55 - ROUND(("MaxTemp" + "MinTemp")/2,1) , 0)


You also asked:
Quote:
Also, is there an equivalent for an Average function in a calculated field? I tried using AVERAGE("MaxTemp","MinTemp") and AVERAGE("MaxTemp;"MinTemp"), but neither worked.


When using an AVERAGE function . . . in database terms, this is an Aggregate Function. Yes, you can use Base to create this. Just a few rules. Each field marked as, VISIBLE ( with the check mark ) must be either:
  1. Average
  2. Sum
  3. Maximum
  4. Minimum
  5. Count
  6. Group - this field ( or multiple fields ), will make it UNIQUE on a row by row basis -- that is, for the Aggregate functions ( perhaps by day in your case, or even month ? ? ? )
Note, you can use Criterion issues on fields NOT in the Aggregate, by UNCHECKING the Visible box.

Furthermore, the Aggregate functions are available on the Aggregate line drop down list.

Check out the following links for examples:
  1. http://www.oooforum.org/forum/viewtopic.phtml?t=67517
  2. http://www.oooforum.org/forum/viewtopic.phtml?t=66790
  3. http://www.oooforum.org/forum/viewtopic.phtml?t=64543
I hope this helps, please be sure to let me / us know. Smile

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
kcheek
Newbie
Newbie


Joined: 21 Feb 2009
Posts: 2

PostPosted: Sun Feb 22, 2009 3:18 pm    Post subject: Reply with quote

It worked. Discovered that you can't reference the alias in another calculated field in the same query. Thus I had to use the formula to calculate MeanTemp in the Heating Degree Days and Cooling Degree Days calculated values.

If I follow you correctly, there isn't an average function that calculates the average of a list of fields in a record, such as Average("MaxTemp","MinTemp"). I didn't see it on the list at the URL of HSQL functions. No problem: I just kept the ROUND(("MaxTemp"+"MinTemp")/2,1).

Thanks for the help.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Feb 22, 2009 3:34 pm    Post subject: Reply with quote

kcheek:

What you have said in the above post, is all true . . . at least . . . to the best of my knowledge.

Now, if it were me ( infamous last words ) . . . what I would do, is:

Create a VIEW . . . that is . . . a VIEW where you have 'calculated' your average . . . including the Primary Key to your table.

This way, you could 'link' the VIEW and your TABLE in a query. So, you will have available, from the VIEW, the 'calculated average' as a field, so, you can than perform your additional calculations, without having to 're-calculate' the average multiple times.

I hope that helps, and, Welcome to the Universe of OpenOffice Base. Smile

Sliderule
Back to top
View user's profile Send private message
carusoswi
Newbie
Newbie


Joined: 14 May 2009
Posts: 4

PostPosted: Sun May 24, 2009 3:43 am    Post subject: Reply with quote

I use MS Office to calculate areas (based upon room wall, floor, ceiling dimensions). I set up two tables, one with a unique ID and descriptive fields about the room, the other contains fields for dimensions, a field for factor (how many times that dimension occurs), etc.

In access, I create a form/subform where table one (with unique ID) is the form, table 2 (non unique id) is the subform. I can than enter the room data once, and, in the subform, the various dimensions/factors that I need to calculate.

All this I have accomplished in OO, although I don't know how to make the unique ID increment automatically (perhaps you can help me with that).

Next, in Access, I create a query including fields from both tables. I then created a calculated field using the expression Area (or WallArea, or CeilingArea, or whatever, based upon my needs). I haven't figured out how to do this in OO. In access, it's simple: AREA: =([width]*[length]*[factor]).

Can you help me with this in OO?

Next, in Access, I create fields with if/then statements to post the products to their respective columns:

iif([ceilingcolor]="pink",[area],0)

So, wherever the field in table one for ceiling color contains the value Pink, that area will get posted to a column that contains area products for only those areas that are scheduled for the color pink.

I proceed to set up other columns for all the various colors or other finish types to complete the query.

I then export this query to excel to quickly calculate the totals.

I have used Access' report generator to produce beautiful reports on these queries, but find that for more complex jobs, it's just too time consuming to get everything to show up on the report the way I want it.

Excel is much more straightforward in creating a report that I can read and print when necessary.

In trying to use OO, I am stumped on those if/then statements and, also, obviously, on the simple matter of creating a column with a user defined alias and a calculated value (such as area).

Can you help?

Thanks in advance.

Caruso
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group