Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
lesson_7 [2019/08/13 16:58] argemiro |
lesson_7 [2020/02/10 21:45] argemiro |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | =====LESSON 7: Calculating a lookup table using algebraic/logical expression involving tables and values===== | + | {{ :logo_logo.png?400 |}} |
+ | \\ | ||
+ | \\ | ||
+ | ====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\Database\Tables\''. Next, open **Load Table** and load ''IBGE_crop_information_uptated.csv''. | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem43.png?200 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * 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”). | ||
+ | \\ | ||
+ | \\ | ||
+ | <note important> | ||
+ | The position where the new column is inserted determines whether the new column is a key or data column. | ||
+ | </note> | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem44.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * 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\Set_2\basics\'' 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: | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem45.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * Save and run the model. Click on eye button of “Save Table” to open the resulting table. | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem46.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | ---- | ||
+ | |||
+ | 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''). | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem47.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * 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 tab Column Index or Name of the Functor Editor window, insert the index of the column to be retrieved (“3” or “harvested area”). | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem48.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * 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 tab Keys of the Functor Editor window, insert the year you want to retrieve the harvested area per crop (in this example, “2004”). | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem49.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * Grab a **Save Table** and place it on the sketch. Connect **Get Table from Key** and **Save Table**. The final model should have the following structure: | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem50.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * To save the result to a file, open **Save Table**, browse to ''GuideBook_Dinamica_5\Models\Set_2\basics\ Get_Table_from_Key'' and write the file name (e.g. IBGE_crop_information_update). | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem51.png?500 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | * 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: | ||
+ | \\ | ||
+ | \\ | ||
+ | {{ :imagem53.png?300 |}} | ||
+ | \\ | ||
+ | \\ | ||
+ | ==== Congratulations, you have successfully completed this lesson! ==== | ||
+ | \\ | ||
+ | ☞[[:lesson_8 | Next Lesson]] | ||
+ | \\ | ||
+ | ☞[[:guidebook_start| Back to Guidebook Start]] |