Difference between revisions of "Documentation/How Tos/Calc: Database functions"

Revision as of 09:09, 1 December 2007

Introduction

In the OpenOffice.org spreadsheet 'Calc', a 'database' is simply a table of values, and has nothing to do with the more complex OpenOffice.org database 'Base'. The 'database' functions in Calc are quite simple to use, and allow you to choose and process data in a table.

A Calc 'database' table might look like this:

The first row of the 'database' table has headings (Name, Grade, Age ... ), and each subsequent row contains the data values.

The 'criteria', which are used to select rows from the 'database', are entered in another table:

The first row of this table has headings. Subsequent rows specify the criteria: for example, using the criteria table above will match those children whose distance to school is more than 600.

The Calc 'database' functions all have a similar form. As a simple first example, using the tables above:

DCOUNT(A1:E10; 0; A13:E14)

returns 5, the number of children whose “Distance to School” is more than 600.

A1:E10 is the database table and A13:E14 is the criteria table.

There are other 'database' functions to return a sum, an average, a standard deviation, and so on.

Criteria

The criteria table may be placed anywhere on the spreadsheet, but it is often wise to have it near the database table.

The criteria table headings must exactly match headings in the database table, but they can appear in any order, and can appear more than once:

All criteria on a row must be satisfied for the row to be satisfied, so in this example above we find rows which have:

“Distance to School” more than 600    AND

“Age” greater than 8    AND

“Age” less than or equal to 10

in other words '9 and 10 year olds who travel more than 600 to school'.

If the criteria table has more than one row of criteria, any one satisfied row means the overall criteria table is satisfied:

In this example the test is:

(“Distance to School” more than 600    AND

“Age” greater than 8    AND

“Age” less than or equal to 10)

OR

( “Age” less than or equal to 8)

In other words we find rows where the children are either 'aged 9 or 10 with 600 or more to travel to school', or 'aged 8 or under'.

The condition which is entered in a cell of a criteria table (eg >4 ) is simply text and has the following form:

comparator value where

comparator is one of >, <, >=, <=, =, <> (if comparator is omitted = is assumed );

value is the value (number or text) to be compared.

For example:

the condition “>4” tests if the contents of cells are greater than 4.

the condition “<lamp” tests if the contents of cells come alphabetically before lamp (so lady and ant meet the condition, but late and zebra do not).

the condition “lamp” tests if the contents of cells are lamp - but Calc has a number of settings which define the exact behaviour - please read the following section:

Settings for text conditions

When matching a simple text condition, (such as “<lamp”) it can be important to check the settings on the Tools menu→Options→OpenOffice.org Calc→Calculate dialog:

Case sensitive

this check box has no effect on Calc database functions. Case is ignored - so that “lamp” will always match lamp, Lamp and LAMP, etc.

Search criteria = and <> must apply to whole cells

if this check box is ticked, “lamp” will only match a cell containing just lamp. If this check box is not ticked, “lamp” will match lamp, clamp, lampoon, etc. - in other words it will match if lamp is found anywhere in the cell's text.

Enable regular expressions in formulas

if this check box is ticked, then the condition is treated as a regular expression. For example the condition “l?amp” would match lamp and amp (in regular expressions “l?” means the “l” is optional).Regular expressions are similar to wildcards, but more powerful; they are described in the HowTo: Regular Expressions in Calc. (Note that the ' Search criteria = and <> must apply to whole cells ' setting above also works when regular expressions are selected.)

The functions results can depend on these settings - but unfortunately they are rather hidden away so that a user may be unaware if the settings are wrong.