inconsistent data extraction

[_Pete]

I've been trying to re-arrange the data from a large database so that the data can be imported into a statistical software package. I've created a new database file and the defined fields based on a relationship (and logical calculation) with the original. It seems to work...partially. Some of the data is correctly extracted and re-arranged, but some is inexplicably (to me) ignored. Can anyone tell me what I'm doing wrong? I've provided a set-by-step description of what I did (and am trying to do) below, though the actual files are very large and rather more complicated (many more fields). Thanks! Pete

objective: re-arrange the data in one file to conform with the set-up in a second

problem: seems only PARTIALLY successful--some of the data is correctly imported from the first into the second, but some is inexplicably ignored

original in table view looks like this:

setID species no_of_fish

382710 110 2905

382710 111 2267

382710 124 1

382712 110 1631

382712 111 1090

382714 110 544

second file in table view looks like

setID YFT SKJ BLM

382710 2905 2267 1

382712 1631 1090 0

382714 544 0 0

[where species 110=YFT, 111=SKJ, 124=BLM]

To create the second file, I...

1. extracted a list of unique setIDs from the original

2. defined a relationship (for the 2nd), 'match setIDs', as follows: setID = ::setID, where the related file was the original

3. created fields for YFT using the following calculation: = If(match setIDs::species = 110, match setIDs::no_of_fish, 0)

4. fields for the remaining species (SKJ and BLM) were created in a similar fashion

[Christian Coppe]

Your relationship will only find the data from the first record, e.g. the record with 382710 110 2905. The data from the two other records identified with 382710 will be ignored. A solution is that you create a relation key in your first file, by combining the setID with the species : key=(setID&species). In the second file, you create a serie of keys, like key110=(setID&110), key111=(setID&111), etc. Create the links for each key : link110=key110::key, link111=key111::key, etc. Then, for each record, you can create calculated fields, each of them being linked to the first file by its unique key. For example : field YFT : if(link110::species<>""; "2905" ; ""), field SKJ : if(link111::species<>""; "2267" ; ""), etc.

HTH

Christian

[dbruggmann]

Hello Pete

I used your question for a little training myself. Attached are two files, which show how you're objective can be automated.

The script in the Original file first exports every SetID value only once which are then imported in the New file. There are the fields c.YFT, c.SKJ and c.BLM set up as calculation fields which get for every species the correct number of fish from the Original file. These fields you could now export for the statistical software package.

DataExtraction.zip

[_Pete]

That's awesome, both of you! Thanks...Pete