Join two worksheets based on one or more sort keys. The object can perform two types of joins: inner and outer. Each one corresponds to inner and outer joins used in SQL (structured query language). The result of an SQL join will be sorted by the sort keys.
Use one of the following to add the object to a Workflow:
Right-click menu for a Workflow object: New > Data Management > Join Worksheets.
Main menu: Insert > Data Management > Join Worksheets.
Right-click menu for a worksheet: Send To > Data Management > Join Worksheets.
Note:To view the object in its own window, select it in the Object Browser and double-click it or press ENTER. All instructions for setting up and execution are the same whether the object is viewed in its own window or in Phoenix view.
Additional information is available for the following topics:
Mappings panel
Sort Map panel
Options tab
Results
The Mappings panel allows users to specify which columns in a worksheet are involved in the worksheet transformation. Required input is highlighted orange in the interface.
Sort: The variable(s) to match by when joining the worksheets.
Source Column: The additional column(s) to include in a new worksheet.
By default, the Join Worksheets object requires that columns mapped to the Sort context have the same name. The Sort Map panel allows users to mix and match or override column names. For example, if the two columns being merged have different names, such as Subject and ID, the Sort Map panel can be used to specify matching columns in the two worksheets.
In this example, the Subject column in worksheet 1 is joined/merged to the ID column in worksheet 2, and the Conc column in worksheet 1 is joined/merged to the YObs column in worksheet 2.
The default join for the Join Worksheets object is an outer join. Check the Inner Join box to use an inner join to combine the two worksheets. Both types of joins combine rows from the worksheets that have matching values for the sort keys, but an inner join does not retain any of the unmatched rows, whereas an outer join also retains the rows from both worksheets that do not have a matching value in the other worksheet.
Note that the Join Worksheet object uses a Cartesian join (also called cross join) in the many-to-many case for matched values, i.e., the case where, for a specific set of values to match on, both worksheets have multiple rows with those values.
The Join Worksheets object generates one worksheet and one text file. The output worksheet is sorted alphabetically or numerically based on the sort keys and the order they were mapped.
Result: The joined worksheets.
Settings: Text file containing input worksheets used and the options selected.
Joining raw data and modeling output in tables example
This example shows how to reproduce table template 9 that was used in WinNonlin 5.3 and earlier. Phoenix does not have a specific table type for this template. The main difference between table template 9 and the other table templates in WinNonlin is that two datasets are joined to create the final output. The Phoenix Table object only works with one dataset at a time. To produce a table similar to table template 9 it is necessary to use Phoenix’s Join Worksheets object prior to creating the table.
In this example, two datasets are joined by the Sort variables in both datasets. The Default table type in Phoenix is used to recreate table template 9 in WinNonlin. These steps include:
Recreating WinNonlin’s table template 9 in Phoenix
This example uses two datasets, clayton.CSV and clayton_pk.dat. clayton.CSV contains time and concentration data for two formulations. clayton_pk.dat contains the Final Parameters output from a noncompartmental analysis.
-
Create a new project named Join.
-
Import the files …\Examples\WinNonlin\Supporting files\clayton.CSV and clayton_pk.dat.
In the File Import Wizard dialog, select the Has units row option for clayton.CSV only. -
Right-click clayton in the Data folder and select Send To > Data Management > Join Worksheets.
-
Map Subject to the Sort context.
Map Form to the Sort context.
Leave Period mapped to None.
Leave Seq mapped to None.
Map Hour to the Source Column context.
Map Conc to the Source Column context. -
Select Worksheet 2 from the Setup list.
-
Drag the clayton_pk worksheet from the Data folder to the Join Worksheets object’s Worksheet 2 Mappings panel and map the input columns to the contexts as follows:
Map Subject to the Sort context.
Map Form to the Sort context.
Map Tmax to the Source Column context.
Map Cmax to the Source Column context.
Map AUClast to the Source Column context.
Leave the rest mapped to None. -
Click
(Execute icon) to execute the object. -
Right-click Workflow in the Object Browser and select New > Reporting > Table.
-
In the Table 1 object’s Mappings panel click
(Select Source icon). -
In the dialog, expand Join Worksheets, select Result, and click OK.
-
Map the input columns to the contexts as follows:
Map Subject to the Row ID context.
Map Form to the Stratification Row context.
Map Hour to the Stratification Column context.
Map Conc to the Data context.
Map Tmax, Cmax, and AUClast to the Dependency context.
Setting up the summary statistics
-
Select the Statistics tab below the Setup panel.
-
Check the checkboxes in the Display column to select the following summary statistics: N, Mean, SE.
-
Select the Options tab.
-
With Table selected in the Options menu tree, check the Page Break on Row Stratification checkbox.
-
Expand Precision/Alignment in the Options menu tree, click Hour and select 1 in the Value menu.
-
Under Precision/Alignment, click Subject in the Options menu tree and select 0 in the Value menu.
-
Enter a title for the table by selecting Titles in the Options menu tree.
– In the Title field type Table 2 and click Add.
– In the Title field type Raw Data and Pharmacokinetic Parameters and click Add.
Executing and viewing the results of the joining
-
Execute the object.
Default table type Formulation c results
Default table type Formulation t results
This concludes the Joining Raw Data and Model Output in Tables example.
