Lookup help

[ranger29]

I *think* this issue involves trying to do a look up that matches the value in one table, to a *field name* in another table (and then finds the corresponding data)… but I may be wrong.

Narrative description of the problem:

I have a dataset of countries that have experienced civil war, and want to be able to look up the GDP per capita for each country for the year of that country's peace agreement. The civil war dataset is my own creation, I get the GDP timeseries data from the World Bank. Naturally, the year of a peace agreement is different for each country.

What I have so far:

I have a Main Table, which includes fields for the country name (CName) and the calendar year of the last Peace Agreement (YrPeaceA), i.e. in excel, each country is a row in the left column. The country name field is the master field - one record for each country. In the excel document downloaded from the World Bank, the country names are also in the left column, with years on the top row, and the GDP per year for each country-year in the rest of the cells.

I can and have created a new FMPro file by importing the GDP data from the World Bank, but the year fields (top row in Excel) change from (for example), "1962" to "_1962" because FM doesn't like just numbers in field names.

How do I do a lookup that finds the GDP value in the World Bank table, matching the country and the calendar year specified in the YrPeaceA field? I managed to successfully add a field in the FM Main Table that adds an underscore to the "YrPeaceA" field, thereby making the values match the WB values (they're actually not values but field names in the WB data, I think that's where my problem is).

I've linked the two tables by country name. Also tried transposing the World Bank data in excel first (making the rows columns and the columns rows so the calendar years are not converted with an underscore in front).

What am I missing? Surely people have done similar lookups before…

If this helps, I believe I can download the WB data as xml (the site gives the option of SDMX format, which I think can become xml.

Way back when (FM 3.0 or so), I would have considered myself advanced intermediate. After years away, I think I'm now a beginner--using FM Pro 12 on a mac. It said I'm not permitted to upload the FM file or the excel file with the World Bank data.

Thanks for any help or ideas.

[Fitch]

You can relate two tables by more than one match field. Change your relationship to match country with country, and add a match of peace year with GDP year. That should enable you to pull the GDP number.

The field names don't have to match, it makes no difference. (But why use a name like "CName" instead of just "Country"?)

Uploaded files here must be zipped first.

[ranger29]

I'm attaching four files--the main FM file, "civil war for help", the original data from the World Bank in excel, and two FM files of the imported WB data--one as is, and the other with the fields transposed (was trying to get around FM adding an underscore before the year).

I'm trying to use shorter names (like "CName" instead of "Country" to train myself to use the shorter one, as I will eventually be using this data in statistical analysis using SPSS, R or some other program. Many other stat programs have restrictions on field names--no spaces, no punctuation, some only up to 8 characters, etc.

civilwarlookup.zip

[eos]

I'm attaching four files--the main FM file, "civil war for help", the original data from the World Bank in excel, and two FM files of the imported WB data--one as is, and the other with the fields transposed (was trying to get around FM adding an underscore before the year).

I'm trying to use shorter names (like "CName" instead of "Country" to train myself to use the shorter one, as I will eventually be using this data in statistical analysis using SPSS, R or some other program. Many other stat programs have restrictions on field names--no spaces, no punctuation, some only up to 8 characters, etc.

I imported the Excel file as a new table, then created another new (join) table GPDPerYearPerCounty_J with fields for country name, year and GPD, and wrote a script that extracts the values from the 52 fields and creates new records in the join table (ca. 12,800). Now a simple relationship by country name and year of peace is enough to display the GPD on the CivilWar layout; no copying necessary. I left the imported table in the file so the references in the script are still readable, but it's no longer necessary, since all its data is now contained within GPDPerYearPerCounty_J.

If you ever again receive data in a format which creates records with a myriad of fields, use a script like this one to create records in a child / join table and bring your data into a (more) normalized form.

civil_war_for_help_normalized.fmp12.zip

[ranger29]

Thank you, eos, and Fitch. I think now my main relationship problem was that I was trying to make it one-to-many, when it really should be many-to-many...

Thank you, eos, for the script, and for showing me what the WorldBank data should look like in order to play happily with FM. I was trying to avoid (re)learning scripts for this, but I think it will be worth my while.

Eos, I saw the Country=Country and PeaceAgreementYear=Year relationships you made, but I can't figure out how you got the GDP for the right year to show up in the GDPforPeaceYear field--I didn't see a lookup or anything in the GDPforPeaceYear field. How did you do it?

I need to learn how to do this well, because I'll be doing it way more than once (I want to be able to find such things as GPD 5 years before the Peace Agreement, and, eventually, things like infant mortality 5 years after the cesssation of violence, levels of free speech two years before a new constitution, etc.)

[eos]

Eos, I saw the Country=Country and PeaceAgreementYear=Year relationships you made, but I can't figure out how you got the GDP for the right year to show up in the GDPforPeaceYear field--I didn't see a lookup or anything in the GDPforPeaceYear field. How did you do it?

The field in question is actually the GDP field from the GPDPerYearPerCounty_J table. You can display any related field on the layout (or use its data for lookups, calculations etc.). In this case, since we know that there's only one record in the GPDPerYearPerCounty_J table for each year/country combination, then a relationship using these two criteria effectively works as a filter which isolates that one entry. If there were more related records, you'd have to use a portal to show fields from all related records.

Speaking of which, if you plan to extend this database, then consider creating a Country table, a Status/EventTypes table, and a CountryEvent join table. In this join table, you can combine any event with any country - even multiple occurrences of the same event for one country, like several civil wars - and specify the respective year. Then you could use the GPDPerYearPerCounty_J table to hold other types of data as well for a country/year/eventType combination, and employ a similar relationship from the join table, using event type as an additional relationship predicate, to find the appropriate GDP/Level of free speech/child mortality/whatever for each country/year combination.

Then use portals to list all events of one country, or, in the EventTypes table, all countries for a given event type, or prepare a summary report in the join table.

[ranger29]

Eos, the World Bank table you imported from excel (WorldBank GDP per Capital.xls_original), in the civil_war_for_help_normalized FM file, has the field names, _1960, _1961, etc, with underscore. But your script that brought the data into the join table seems to refer to to just a "year" field in the imported World Bank table... how did you do that?

I know the World Bank excel file had an original worksheet, and a worksheet where the data was transposed (thereby enabling a field called "year"), but it doesn't seem like the transposed worksheet is the one you imported.

[eos]

Eos, the World Bank table you imported from excel (WorldBank GDP per Capital.xls_original), in the civil_war_for_help_normalized FM file, has the field names, _1960, _1961, etc, with underscore. But your script that brought the data into the join table seems to refer to to just a "year" field in the imported World Bank table... how did you do that?

Actually, the script uses a field name that is calculated new for each loop as a concatenation of "_" and the loop counter, which results in a name like “_1961” to use with GetField(). The “year” field is the one in the join table.

[ranger29]

Got it. I see where that is now in the script. That concatenation is part of the magic I was looking for. Thank you!