Create a Derived Table

CONTENTS

Home

This derived table pulls the data for Winner and Loser names from the atp_matches and atp_players tables using the player Id.

  1. Start from the ATPTennisData Project. Select: Projects Projects > ATPTennisData
  2. Click Add atp_matches table to add the existing Players atp_matches table from the datasource.
  3. Click Create File(s).
  4. Add a new table using the Add Tables Add a new table icon.
  5. Name the table MatchesAndPlayers. Click Create the file to create the file.
  6. Add the SQL to define where you are going to fetch the data.
table MatchesAndPlayers
  data_source TennisData
  sql """ SELECT m.tournament, m.Surface, m.WinnerId, m.LoserId,m.Score, m.Round,
          m.Minutes,
      concat(p.Fname, ' , ', p.LName) as WinnerName, concat(pl.FName, ' , ',) 
          as LoserName
          FROM players.atp_matches m join players.atp_players p on m.winnerid = p.id
               left join players.atp_players pl on m.loserid = pl.id """
  1. Add the column definitions:
  column tournament
    sql tournament
    type string
  column Surface
    sql Surface
    type string
  column WinnerId
    sql WinnerId
    type int
  column LoserId
    sql LoserId
    type int
  column Score
    sql Score
    type string
  column Minutes
    sql Minutes
    type int
  column WinnerName
    sql WinnerName
    type string
  column LoserName
    sql LoserName
    type string
  column number_of_rows
    sql number_of_rows
    type int
  1. Create a RoundName column to make the existing Round abbreviations user-friendly:
column RoundName
  sql """ case when (Round = 'QF') then 'Quarter Final' else
                (case when (Round = 'SF') then 'Semi Final' else
                  (case when (Round = 'F') then 'Final' else '' end )
                 end)
              end """
  type string
  1. Click Save.
  2. Edit the ATPTennisData dataset:
    • Add atp_matches and MatchesAndPlayers to table
    • Add the join for atp_matches and atp_players. No joins are required for the new MatchesAndPlayers table.
    Join the tables
  3. Click Save.
  4. Click Commit.
  5. Click Deploy.

In the Create the Visualizations tutorials, a new sheet is created for a tabular data visualization of the new derived table.

IN THIS PAGE