Skip to main content Skip to complementary content

Creating a hierarchical map from a flat structure

This feature allows you to create a hierarchical structure based on an existing flat structure and automatically map them together.

Before you begin

You have created a flat structure.

About this task

In this example, the input is a CSV file containing information about actors and actresses who have won Oscars between 1990 and 2016. The goal of the transformation is to get a JSON file in which the data is grouped by year, then by movie. The record for each movie will contain an array of the actors who won an Oscar for that movie.

You can create the input CSV file from the following data:
Index,Gender,Year,Age,Name,Movie
1,female,1990,80,Jessica Tandy,Driving Miss Daisy
2,female,1991,42,Kathy Bates,Misery
3,female,1992,29,Jodie Foster,The Silence of the Lambs
4,female,1993,33,Emma Thompson,Howards End
5,female,1994,36,Holly Hunter,The Piano
6,female,1995,45,Jessica Lange,Blue Sky
7,female,1996,49,Susan Sarandon,Dead Man Walking
8,female,1997,39,Frances McDormand,Fargo
9,female,1998,34,Helen Hunt,As Good as It Gets
10,female,1999,26,Gwyneth Paltrow,Shakespeare in Love
11,female,2000,25,Hilary Swank,Boys Don't Cry
12,female,2001,33,Julia Roberts,Erin Brockovich
13,female,2002,35,Halle Berry,Monster's Ball
14,female,2003,35,Nicole Kidman,The Hours
15,female,2004,28,Charlize Theron,Monster
16,female,2005,30,Hilary Swank,Million Dollar Baby
17,female,2006,29,Reese Witherspoon,Walk the Line
18,female,2007,61,Helen Mirren,The Queen
19,female,2008,32,Marion Cotillard,La Vie en rose
20,female,2009,33,Kate Winslet,The Reader
21,female,2010,45,Sandra Bullock,The Blind Side
22,female,2011,29,Natalie Portman,Black Swan
23,female,2012,62,Meryl Streep,The Iron Lady
24,female,2013,22,Jennifer Lawrence,Silver Linings Playbook
25,female,2014,44,Cate Blanchett,Blue Jasmine
26,female,2015,54,Julianne Moore,Still Alice
27,female,2016,26,Brie Larson,Room
28,male,1990,32,Daniel Day-Lewis,My Left Foot
29,male,1991,42,Jeremy Irons,Reversal of Fortune
30,male,1992,54,Anthony Hopkins,The Silence of the Lambs
31,male,1993,52,Al Pacino,Scent of a Woman
32,male,1994,37,Tom Hanks,Philadelphia
33,male,1995,38,Tom Hanks,Forrest Gump
34,male,1996,32,Nicolas Cage,Leaving Las Vegas
35,male,1997,45,Geoffrey Rush,Shine
36,male,1998,60,Jack Nicholson,As Good as It Gets
37,male,1999,46,Roberto Benigni,Life Is Beautiful
38,male,2000,40,Kevin Spacey,American Beauty
39,male,2001,36,Russell Crowe,Gladiator
40,male,2002,47,Denzel Washington,Training Day
41,male,2003,29,Adrien Brody,The Pianist
42,male,2004,43,Sean Penn,Mystic River
43,male,2005,37,Jamie Foxx,Ray
44,male,2006,38,Philip Seymour Hoffman,Capote
45,male,2007,45,Forest Whitaker,The Last King of Scotland
46,male,2008,50,Daniel Day-Lewis,There Will Be Blood
47,male,2009,48,Sean Penn,Milk
48,male,2010,60,Jeff Bridges,Crazy Heart
49,male,2011,50,Colin Firth,The King's Speech
50,male,2012,39,Jean Dujardin,The Artist
51,male,2013,55,Daniel Day-Lewis,Lincoln
52,male,2014,44,Matthew McConaughey,Dallas Buyers Club
53,male,2015,33,Eddie Redmayne,The Theory of Everything
54,male,2016,41,Leonardo DiCaprio,The Revenant

Procedure

  1. In the Data Mapper tab of the Mapping perspective, right-click Maps in Hierarchical Mapper and click New > Map.
  2. Select Flat to hierarchical map and click Next.
  3. Select a folder and enter a name for the map, then click Next.
  4. Select your flat input structure and click Next.
  5. Select a folder and enter a name for the new structure, then click Next.
    The output structure editor opens. By default, it is identical to the input. You can now customize it.
  6. Edit the output structure to create the hierarchy.
    For more details about the features available, see Hierarchical output structure editor.
    1. Click the row element twice to rename it, and enter the name Oscars.
    2. Right-click Oscars and click Convert to GroupBy.
      By default, the loop is grouped by its first child element.
    3. Right-click Year and click Select as GroupBy element to group the Oscars loop by year.
    4. Since the Index element is not needed, right-click it and click Delete.
    5. Right-click Movie and click Add GroupBy to create a new loop grouped by movie.
    6. Select the elements Name, Gender and Age, then right-click them and click Add Record. Name the record Actor and click OK.
      This creates a new record containing the selected elements. You can reorder the elements in the record with using drag and drop.
    7. Right-click the new Actor element and click Add Array, then rename the array as Actors.
    8. Drag and drop the Actors array on the Movie loop.
    9. Rename the Movie_groupby element as Movies.
    The structure should look like this:
  7. Once you have finished your edits, click Finish.

Results

The map is generated; you can click the output elements to see the expressions generated.
You can click Test Run to get a preview of the output. In this example, the output looks like this:
{
    "Oscars": [
        {
            "Year": "1990",
            "Movies": [
                {
                    "Movie": "Driving Miss Daisy",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jessica Tandy",
                                "Gender": "female",
                                "Age": "80"
                            }
                        }
                    ]
                },
                {
                    "Movie": "My Left Foot",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Daniel Day-Lewis",
                                "Gender": "male",
                                "Age": "32"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1991",
            "Movies": [
                {
                    "Movie": "Misery",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Kathy Bates",
                                "Gender": "female",
                                "Age": "42"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Reversal of Fortune",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jeremy Irons",
                                "Gender": "male",
                                "Age": "42"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1992",
            "Movies": [
                {
                    "Movie": "The Silence of the Lambs",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jodie Foster",
                                "Gender": "female",
                                "Age": "29"
                            }
                        },
                        {
                            "Actor": {
                                "Name": "Anthony Hopkins",
                                "Gender": "male",
                                "Age": "54"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1993",
            "Movies": [
                {
                    "Movie": "Howards End",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Emma Thompson",
                                "Gender": "female",
                                "Age": "33"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Scent of a Woman",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Al Pacino",
                                "Gender": "male",
                                "Age": "52"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1994",
            "Movies": [
                {
                    "Movie": "Philadelphia",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Tom Hanks",
                                "Gender": "male",
                                "Age": "37"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Piano",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Holly Hunter",
                                "Gender": "female",
                                "Age": "36"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1995",
            "Movies": [
                {
                    "Movie": "Blue Sky",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jessica Lange",
                                "Gender": "female",
                                "Age": "45"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Forrest Gump",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Tom Hanks",
                                "Gender": "male",
                                "Age": "38"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1996",
            "Movies": [
                {
                    "Movie": "Dead Man Walking",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Susan Sarandon",
                                "Gender": "female",
                                "Age": "49"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Leaving Las Vegas",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Nicolas Cage",
                                "Gender": "male",
                                "Age": "32"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1997",
            "Movies": [
                {
                    "Movie": "Fargo",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Frances McDormand",
                                "Gender": "female",
                                "Age": "39"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Shine",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Geoffrey Rush",
                                "Gender": "male",
                                "Age": "45"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1998",
            "Movies": [
                {
                    "Movie": "As Good as It Gets",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Helen Hunt",
                                "Gender": "female",
                                "Age": "34"
                            }
                        },
                        {
                            "Actor": {
                                "Name": "Jack Nicholson",
                                "Gender": "male",
                                "Age": "60"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "1999",
            "Movies": [
                {
                    "Movie": "Life Is Beautiful",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Roberto Benigni",
                                "Gender": "male",
                                "Age": "46"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Shakespeare in Love",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Gwyneth Paltrow",
                                "Gender": "female",
                                "Age": "26"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2000",
            "Movies": [
                {
                    "Movie": "American Beauty",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Kevin Spacey",
                                "Gender": "male",
                                "Age": "40"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Boys Don't Cry",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Hilary Swank",
                                "Gender": "female",
                                "Age": "25"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2001",
            "Movies": [
                {
                    "Movie": "Erin Brockovich",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Julia Roberts",
                                "Gender": "female",
                                "Age": "33"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Gladiator",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Russell Crowe",
                                "Gender": "male",
                                "Age": "36"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2002",
            "Movies": [
                {
                    "Movie": "Monster's Ball",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Halle Berry",
                                "Gender": "female",
                                "Age": "35"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Training Day",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Denzel Washington",
                                "Gender": "male",
                                "Age": "47"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2003",
            "Movies": [
                {
                    "Movie": "The Hours",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Nicole Kidman",
                                "Gender": "female",
                                "Age": "35"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Pianist",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Adrien Brody",
                                "Gender": "male",
                                "Age": "29"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2004",
            "Movies": [
                {
                    "Movie": "Monster",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Charlize Theron",
                                "Gender": "female",
                                "Age": "28"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Mystic River",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Sean Penn",
                                "Gender": "male",
                                "Age": "43"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2005",
            "Movies": [
                {
                    "Movie": "Million Dollar Baby",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Hilary Swank",
                                "Gender": "female",
                                "Age": "30"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Ray",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jamie Foxx",
                                "Gender": "male",
                                "Age": "37"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2006",
            "Movies": [
                {
                    "Movie": "Capote",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Philip Seymour Hoffman",
                                "Gender": "male",
                                "Age": "38"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Walk the Line",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Reese Witherspoon",
                                "Gender": "female",
                                "Age": "29"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2007",
            "Movies": [
                {
                    "Movie": "The Queen",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Helen Mirren",
                                "Gender": "female",
                                "Age": "61"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Last King of Scotland",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Forest Whitaker",
                                "Gender": "male",
                                "Age": "45"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2008",
            "Movies": [
                {
                    "Movie": "La Vie en rose",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Marion Cotillard",
                                "Gender": "female",
                                "Age": "32"
                            }
                        }
                    ]
                },
                {
                    "Movie": "There Will Be Blood",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Daniel Day-Lewis",
                                "Gender": "male",
                                "Age": "50"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2009",
            "Movies": [
                {
                    "Movie": "Milk",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Sean Penn",
                                "Gender": "male",
                                "Age": "48"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Reader",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Kate Winslet",
                                "Gender": "female",
                                "Age": "33"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2010",
            "Movies": [
                {
                    "Movie": "Crazy Heart",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jeff Bridges",
                                "Gender": "male",
                                "Age": "60"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Blind Side",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Sandra Bullock",
                                "Gender": "female",
                                "Age": "45"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2011",
            "Movies": [
                {
                    "Movie": "Black Swan",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Natalie Portman",
                                "Gender": "female",
                                "Age": "29"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The King's Speech",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Colin Firth",
                                "Gender": "male",
                                "Age": "50"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2012",
            "Movies": [
                {
                    "Movie": "The Artist",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jean Dujardin",
                                "Gender": "male",
                                "Age": "39"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Iron Lady",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Meryl Streep",
                                "Gender": "female",
                                "Age": "62"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2013",
            "Movies": [
                {
                    "Movie": "Lincoln",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Daniel Day-Lewis",
                                "Gender": "male",
                                "Age": "55"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Silver Linings Playbook",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Jennifer Lawrence",
                                "Gender": "female",
                                "Age": "22"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2014",
            "Movies": [
                {
                    "Movie": "Blue Jasmine",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Cate Blanchett",
                                "Gender": "female",
                                "Age": "44"
                            }
                        }
                    ]
                },
                {
                    "Movie": "Dallas Buyers Club",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Matthew McConaughey",
                                "Gender": "male",
                                "Age": "44"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2015",
            "Movies": [
                {
                    "Movie": "Still Alice",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Julianne Moore",
                                "Gender": "female",
                                "Age": "54"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Theory of Everything",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Eddie Redmayne",
                                "Gender": "male",
                                "Age": "33"
                            }
                        }
                    ]
                }
            ]
        },
        {
            "Year": "2016",
            "Movies": [
                {
                    "Movie": "Room",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Brie Larson",
                                "Gender": "female",
                                "Age": "26"
                            }
                        }
                    ]
                },
                {
                    "Movie": "The Revenant",
                    "Actors": [
                        {
                            "Actor": {
                                "Name": "Leonardo DiCaprio",
                                "Gender": "male",
                                "Age": "41"
                            }
                        }
                    ]
                }
            ]
        }
    ]
}

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!