Create a Derived Table

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