Simple Example: FAOSTAT (Pivot
TableFormat)
The "pivot tables" generated by FAOSTAT require modification before they can be used to link to tables from other sources. This modification is substantially more complex than that involved in modifying the UNDP data in the first example. This section will deal only with formatting issues.The pivot table downloads often present one or more columns in which two data fields are entered, depending on the options selected for the download. Often one variable is a text and the other a numeric variable-- for example the country name and country code ( Argentina [9] ). These fields need to be modified to assign each of the variables to a separate column (field) if there are plans to use the table for reporting or for linking to other non-FAO tables.
This modification will involve the use of several functions including LEN, LEFT, MID, FIND, VALUE as well as Copy/Paste Values. The examples will first involve demonstrating the step by step set up of the calculations followed by the presentation of a single formula which carries out all of the calculations in one field. Users who are not familiar with using functions will be far more comfortable with the step by step presentations, while the single formula approach requires somewhat more experience dealing with the functions. Note that the order of the calculations and approach to the solution are not unique-- these complex calculations can be approached in different order and even using different functions. It would for example have been possible to set up a calculation using the RIGHT function to separate out the second variable.
For this example we will separate the country and country code files in the example below.
The calculations will be carried out in two phases, the country name will be extracted followed by the country code. Each set of calculations will be carried out step by step then a combined function will be demonstrated.
The extraction of the country name and the country code, including the derivation of the single solution equation, will each require 4 steps. It will be necessary to add 9 blank columns after Column A to the data displayed above to provide room for the calculations.
1) Using FIND determine the position of the first "[" (Column B in the two charts below.
2) Using LEFT and the results of step 1), display the country name. This will also include subtracting 1 from the number calculated in 1) to cut the "[" out of the display. (Column C ).
3) Combine 1) and 2) into a single equation. (Column D ).
4) Copy the results to Column E using the Copy/Paste values function.
Column D represents the combination of the steps in a single equation
The extraction of the country code, including the derivation of the single solution equation, will require 4 steps, including the copying of steps already carried out in the display of the country name.
1) In Column F, determine the number of characters in the data in Column A using the LEN function.
2) In Column F use the MID function to extract the Country Code. The start number is drawn from Column B with a one added to account for the "[" , and length is calculated by subtracting column B from column A and adjusting by -1.
3) Because the country code is numeric and the extracted value is in text format, VALUE is used in Column H to convert the result in Column G from text to numeric.
4) Copy and paste (special, value) the results in Column H to Column J to convert the equation to a number.
Column I represents combination of steps in a single equation.
The two tables below represent the calculations carried out. Table 1 shows the formulas while Table 2 shows the results.
The base table can now be restructured with Column E becoming Column A "Country" and Col J becoming Column B "Country_code". Old Column A and the columns containing formulas can be deleted.
Table1
Table 2
