Joining Data Tables in STATISTICA

Written by: Jennifer Thompson

When data come from multiple sources, such as database tables, it can become necessary and beneficial to join or merge those tables to get the maximum information from our data. In this article, I will look at ways to bring data sources together easily in STATISTCA.

Specifically, we will show how inner and outer joins in queries can achieve this goal. Then we will show how the merge tool in STATISTICA can do the same tasks, both via interactive dialog boxes and the workspace.

Joins in Queried Data

When data reside in databases, a query is needed to bring the data into STATISTICA for analysis. During the query, functions such as an inner join or outer join can bring the data together. When joining two or more tables, a reference field from each table is needed. An inner join returns records where matches were found on this reference field in both tables. Records are discarded when a match from the other table is not found. (Inner joins can be built in STATISTICA with the GUI Query Builder tool.) For an outer join, records without a match in the joining table are returned. This is based on the type of outer join used: left, right or full.  (Outer joins in STATISTICA queries can be performed in Text Mode in the query tool.) See the simple example below:

sample data tables

Inner join results are shown below. Only complete records are returned, they were found in both tables.

ID Data First Name Last Name
1 65 Sally Smith
5 45 Joe Jones


This join was built with the GUI STATISTICA Query tool as seen here.

Query inner join

Full outer join results are shown below. All records are returned from both tables.

ID Data First Name Last Name
1 65 Sally Smith
2 35    
4 86    
5 45 Joe Jones
6   Pete Adams


These results were found with this query statement, seen below:

Merging Data Interactively

The same concepts can be used with data already found in STATISTICA spreadsheets. The Merge tool found on the Data tab in the Manage group can do this as well. In the Merge Options dialog box, the two data spreadsheets are selected with the File 1 and File 2 buttons. Then, change the Mode to Match variables. For an inner join style of merge, select the Unmatched Cases option, Delete cases. For an outer join style of merge, use the default Unmatched Cases option, Fill with MD.

Merge interactively

Merging Data in the Workspace

This merge task can also be performed in the STATISTICA Workspace. First, both data tables should be inserted into the workspace by clicking Data Source. The column to be used for joining the data tables should be selected as the Dependent, continuous variable in each data source.

Then, using the Node Browser, select the Comparing and Merging Multiple Data Sources folder to find the Merge Variables node.

node browser

Next, we need to edit parameters of the Merge Variables node. Double click the node to display the Edit Parameters dialog box. Change the Mode to Relational. For an inner style join, the Unmatched cases should Delete, for an outer style join, Fill with MD.

When the selections are made, run the workspace to create the joined spreadsheet.

These operations are essential to creating the needed tables for analysis. With STATISTICA, you have several paths to choose from for meeting the end goal.



About statsoftsa

StatSoft, Inc. was founded in 1984 and is now one of the largest global providers of analytic software worldwide. StatSoft is also the largest manufacturer of enterprise-wide quality control and improvement software systems in the world, and the only company capable of supporting its QC products worldwide, with wholly owned subsidiaries in all major markets (StatSoft has 23 full-service offices, on all continents), and its software is available in more than 10 languages.

Posted on October 17, 2012, in Uncategorized. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: