How to Add Data Tables


Data can be added to the analysis in several different ways: as new columns, as new rows or as new data tables. Adding data as separate data tables is useful if the new data is unrelated to the previously opened data table or if the new data is in a different format (pivoted vs. unpivoted).

If you have a visualization made from a particular data table which has filtering and marking that you would like to apply to visualizations made from another data table, then you must define a relation between the two tables. For a relation to be useful, you need to have one or more key columns (identifier columns) available in both data tables, and use these to define which rows in the first data table will correspond to rows in the second data table. If you need more than one key column to set up a unique identifier, you must add one relation for each identifier column.

To combine data from different data tables in one visualization, the data tables are loaded as usual, but at least one column must match between the data tables in order to combine the data from the data tables. Columns match if they have the same data type. If two columns are of the same data type and have the same name, Spotfire will match them automatically during loading. You can view and edit column matches in the Data Table Properties dialog. To learn more about using many data tables in the same visualization, see Multiple Data Tables in One Visualization.

In some cases when you need to bring in-memory data from different data sources together in any other single visualization, it may be more suitable to use the Insert Columns or Insert Rows tools. And with in-database data tables you can often join several database tables into a single virtual data table before adding it to Spotfire. See Details on Views  in Connection for more information.

Tip: For a simple line from a different data table in a scatter plot, see Details on Line from Data Table.

  1. Select File > Add Data Tables....

    Response: The Add Data Tables dialog is displayed.

  2. Click Add and select the type of data to add from the drop-down list. Use the search field to limit the options shown.

    Comment: You can add data tables from files, information links,  the clipboard, external connections, databases, data functions or from current data tables within your analysis. You may also have access to other sources if they have been set up by your administrators.

    Response: Depending on your selection you will be presented with a dialog where you can specify which file, information link, etc., to add. If you need more information on specific data sources, see Opening a Text File, Opening an Excel File, Opening a SAS File, Opening an Information Link,  Opening Data from a Database or Adding Data Connections to an Analysis.

  3. Select the source data and specify any required settings.

  4. If desired, specify a new Name for the data table.

  5. If the selected source is a data connection you can change the Load method of the data table.

  6. If the selected source is a (non-cube) data connection or an information link you may want to Load on demand.

  7. Optionally, apply transformations.

  8. Comment: Not applicable for in-database data tables during loading of data, but you can select to import the data from a connection and then use Insert Transformations if the data needs modification.

  9. If you want to add more data tables, repeat steps 2-7 for each data table.

  10. Determine whether or not the new data tables will be related to each other or to previously added data tables. If a relation is necessary, click Manage Relations... and specify the relation.

    Comment: See To define a new relation below for more information. Remember that you need to define a relation if the new data table is to be used to create details visualizations for the previously added data tables.

  11. Click OK.

    Response: The new data tables are incorporated into the analysis and are ready to be used.

  1. In the Add Data Tables dialog, click Manage Relations....

    Response: The Manage Relations dialog is displayed.

  2. Click on New....

    Response: The New Relation dialog is displayed.

  3. Select the two data tables you want to connect from the Left data table and Right data table drop-down lists.

  4. Select the columns containing the identifiers from the Left column and Right column drop-down lists.

  5. If desired, you can apply a Left method or Right method to modify the values of one or both columns.

    Comment: For example, if the identifiers are written in uppercase letters in one of the data tables and in lowercase letters in the other, you can use the Lower method on the uppercase column and change the letters to lowercase.

    Response: The result of the method application is shown in the Sample field.

  6. Click OK.

Tip: You can always go back and edit relations as well as create new ones using the Data Table Properties dialog.

See also:

Transforming Data

Loading Data on Demand