[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
kcheek
Newbie

Joined: 21 Feb 2009
Posts: 2

 Posted: Sat Feb 21, 2009 4:58 pm    Post subject: [SOLVED] Calculated Fields and If-Then Else 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
Sliderule
Super User

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

Posted: Sat Feb 21, 2009 5:07 pm    Post subject:

kcheek:

Welcome to the world of OpenOffice Base.

 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)

 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:I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
kcheek
Newbie

Joined: 21 Feb 2009
Posts: 2

 Posted: Sun Feb 22, 2009 3:18 pm    Post subject: 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.
Sliderule
Super User

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

 Posted: Sun Feb 22, 2009 3:34 pm    Post subject: 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. Sliderule
carusoswi
Newbie

Joined: 14 May 2009
Posts: 4

 Posted: Sun May 24, 2009 3:43 am    Post subject: 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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