Editing join details

Learn how you can change the specifications of existing table joins in CDP Data Visualization.

To demonstrate how to create new data joins, we used dataset Flight Delays, based on data previously imported into Arcadia from the datafile flights-2014.zip

The additional datafiles are airline-id.csv, airport-codes.csv, cancellation-code.csv, airport-lat-long.csv, and state-abbreviations.csv.

  1. Navigate to the Data Model page of the dataset.
  2. Click Edit Data Model to edit the data model.
  3. Click the (link) icon beside the state_abbreviations connection.
    Clicking the join to edit

    The Join Details modal window appears.

  4. Click Edit Join.
    Clicking to edit the join
  5. In the Edit Join modal window, the following options are available:
    1. [Optional] Click Clear Fields to clear all already defined joins between the two tables.
    2. [Optional] Click sample data to preview the data. Click again to hide sample data.
    3. [Optional] Click Add Join Pair to add another column connection between the same two tables.
    4. [Optional] Click Add Join Expression to add a join between the two tables based on a custom SQL expression.
    5. [Optional] Click icon (minus) to remove an existing join pair or an existing join expression.
    6. [Optional] Under Join Expressions, click the text box to open the Join Expression interface. There, specify or update a custom SQL expression that defines the join conditions.
    7. Click Apply to save the changes.
    Clicking to edit the join
  6. To add a a join expression and replace the original field:field join, perform the following steps in the Edit Join modal window:
    1. Remove the initial join between the two columns by clicking the (minus) icon.
    2. Under Join Expressions, click the text box to open the Join Expression interface.

      Here you can specify or update the custom SQL expression that defines the join conditions.

    Deleting the initial join and clicking the Join Expressions box
  7. In the Join Expression modal window, perform the following steps:
    1. Enter the following expression to show only flights that have significant arrival delay, more than five minutes:
      [deststate]=[abbreviation] AND [arrdelay] > 5
    2. Click Apply to save the expression and return to the Edit Join modal window.
    Entering an expression in the Join Expression modal window
  8. In the Edit Join modal window, perform the following steps:
    1. Verify that the initial join between the two columns is deleted and the new join expression appears under Join Expressions.
    2. Click Apply.
    Entering an expression in the Join Expression modal window

    The Data Model interface appears. You can click Show Data to display the updated table.

  9. [Optional] To revert this change prior to saving, click Undo.
  10. Click Save.