Linking Datasheets

The two examples in this section show a simple use of a table ‘lookup’ to translate codes to meaningful names, and a slightly more interesting project, where we plot height against age, having calculated the boys’ ages using a lookup table with their respective dates of birth.

Handling coded data

Anyone whose job or hobby involves recording things and making lists (Trainspotters and Birdwatchers are good examples) usually has a coded master-list so that all they need to record on the day was ‘where I was’ and ‘what I saw’ as a simple pair of numbers. This makes for efficient record-keeping, but is a real nuisance when you want to ask questions like “how many places did we see Herring Gulls last year?”. Here is the kind of datasheet that a typical birder would create:

Of course there is a masterlist of sites ...

... and a masterlist of bird-codes and names (part of which is shown below):

Recording the data this way does have considerable benefits – it enforces consistency and it allows someone at ‘head office’ to decide that from now on Lapwings should really be called Peewits. It doesn’t matter how many lists you have – the master is only in one place so you only need to make one change. However it would be nice to see the ‘translated’ names alongside the raw data in the sheet – this is exactly what the Lookup calculation does, so to experiment with it you can open the Birdlog project and flip to the Calc tab.

It is worth going through this first calculation in detail – Lookup is a very powerful tool (it is what the database people call a ‘relational join’) but can be quite hard to understand. A helpful trick here is to flip to the Data tab and hit F11 on both the source and target datasheets – this way you can arrange them in floating windows so you can see exactly what your columns are as you develop the calculation.

Property	Value
Lookup	Bird
Look in	Bird_id
Picking from	Bird_name
Assign to	Name

The first entry says that we want to look up every entry in the Bird column – in this case the numbers 22,71,194 ... 71 in the Log sheet. The column to look in is Bird_id, which has each number occurring once and once only. This gives us the positions of each of our logged birds in the master list (9,11 and so on in this example), so the next stage is to use these positions as an index to select the corresponding bird names – this is what the Picking from entry specifies. Now we have a list of bird names (or blank items if we got the number wrong) of exactly the same length as the original list of numbers – we can assign this to a new column in the log, which I have called Name. If we do the same for the list of places, we get an enhanced bird log which now looks like:

Now you can simply type in the numbers and have GraPL show you the names! What is more we can use this table very easily to make some pretty handy summaries, for example:

You can experiment with a variety of different summaries here, for example swopping around the ‘Group’ and ‘Categorise’ entries to have the birds as the key and the reserves in the stacked bars.

This example shows one other useful capability of GraPL – you can use field-codes in headings, subheadings and footnotes to embed extra bits of information. This might just be a footnote like “Printed on {date} by {user}” or in this case we have actually included some calculation results by setting these properties to:

Heading	Total Number of Species Seen = {Count distinct Bird}
Subhead	Number of records logged this year = {Count Bird}

You will need to refer to the reference manual (or help file) for a full list of calculations, but here you can see that I counted the number of entries in the birdlist twice, but in the major heading I reduced the list to the unique entries first.

Calculating age from measurement date

The final example in this section uses a column lookup to answer an apparently simple question – which of the children has been growing faster? We have measurements taken from the kitchen doorpost, giving name, date and height, so it is very easy to draw a comparative chart showing their heights against time:

But how do we slide the lines together to get a proper comparison showing their relative growth rates? A good start would be to add a new table which simply has the names and their respective dates of birth:

Now we can create an extra column in the main sheet which simply repeats the date of birth for each child, and then it is easy – just subtract this column from the date of measurement to give their respective ages in days on the date the measurement was taken. Actually, I divided by 365.25 to get the value in years – which you can see here:

As for how it was done:

The only thing to note here is that I did need to fully-qualify the column names as I happened to use the same name in both datasheets.

Continue to: Summarising Data Graphically