LESSON 7: Creating a new column and retrieving a data column from tables


  • To create a new column in a given table we must open all the files that will be used to retrieve data from. Click on the Input/Output tab from the library window and grab three Load Lookup Table Functors and one Load Table placing them on the sketch. Open the Load Lookup Table functors and load the files 1999_production_value.csv, 1999_harvested_area.csv and 1999_production.csv from the folder Guidebook_Dinamica_5\Database\Tables\. Next, open Load Table and load IBGE_crop_information_uptated.csv.





  • Now we can create a new column merge between the 1999_harvested_area.csv and 1999_production.csv files. On the Input/Output tab, grab an Add Table Column and place it on the sketch. Connect the Load Lookup Table which contains the file 1999_harvested_area.csv to Add Table Column, selecting Table as an input port. On the Functor Editor window, write a name for the created column (e.g. “production_value”).



The position where the new column is inserted determines whether the new column is a key or data column.





  • Connect the Load Lookup Table which contains the file 1999_production.csv to Add Table Column, the Column Values port will be automatically assigned as an input port.


  • Add another Add Table Column to the sketch, assign the output of the previous Add Table Column to the Table input port of the recently added functor. Connect the Load Lookup Table with 1999_production_value.csv to the new Add Table Column. In the Functor Editor window, choose a name for the created column (e.g. “production”).


  • Add a Set Table By Key to the sketch, assign the output of Add Table Column to the Subtable input port of Set Table By Key and the Load Table with IBGE_crop_information_uptated.csv to the Table port. In the Functor Editor window, enter 1999 as the key identifying the sub-table that will be updated or inserted. Additionally, toggle the Ignore Column Names flag in the Functor Editor Window.


  • To save the result to a file, place a Save Table on the sketch, browse to \Guidebook_Dinamica_5\Models\add_table_column_set_table_by_key and write the file name (e.g. IBGE_crop_information_complete).


  • Click on the layout tool Execute Layout. Your final model should be similar to the one below:





  • Save and run the model. Select the Save Table functor and click on the eye button to open the resulting table.






Using the table generated previously, we would like to demonstrate how to retrieve a data column from it.

  • Grab a Load Table, placing it on the sketch. Double click it and open the file generated in last step (e.g. IBGE_crop_information_complete.csv).





  • To retrieve a data column corresponding to the harvested area, we will use Get Table Column. Connect the Load Table to Get Table Column. In the input Column Index or Name of the Functor Editor window, insert the index of the column to be retrieved (“3” or “harvested area”).





  • Now, we can retrieve the harvested area per crop corresponding to the year of 2004. For this, grab a Get Table from Key and place it on the sketch. Connect Get Table Column to Get Table from Key. In the input Keys of the Functor Editor window, insert the year you want to retrieve the harvested area per crop (in this example, “2004”).









  • To save the result to a file, edit Save Table, browse to \Guidebook_Dinamica_5\Models\add_table_column_set_table_by_key and write the file name (e.g. IBGE_crop_information_update).





  • Save and run the model. To see the resulting table, click on eye button of Save Table. This functor output is a table that looks like this:





Congratulations, you have successfully completed this lesson!