Appending Tables from the Prepare Window
From the Prepare Window, you can append tables by selecting them from the Table List (use Ctrl+click to select more than one table), right-clicking on your mouse, and then selecting an Append operation from the context menu that displays.
Notes:
-
Different options will produce different results.
-
In some cases, the order in which you select tables to append will affect the result.
-
In the Prepare window table list, select the tables you want to append. Ctrl+click on the tables to select more than one table.
-
Right click, select Append Tables, and the then select the append method:
-
Match Columns on Name
-
Match Columns on Name and Type
-
Match Columns on Order
-
Match Columns on Order, Name and Type
-
Note: If you want more control with defining the append, consider using the Edit Append dialog.
Examples: Appending Tables by Matching on Name
Example 1
Consider the following tables to append.
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
Match Columns on Name produces the following result:
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
|
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
|
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
|
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
|
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
|
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
In this example, the Department column values of the Marketing and Accounting tables are not included because they do not match the name of the first column in the first table.
Monarch Data Prep Studio considers the first table as the basis for appending succeeding tables.
If you append the tables above in a different order, i.e.:
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
You get this result instead:
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
|
|
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
|
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
|
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Example 2
The column order does not matter when you append by Match Columns on Name. Thus, if you consider the following tables:
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
Data Processing
|
Last Name |
First Name |
Emp ID |
Date |
Department |
Gender |
|
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
Data Processing |
M |
|
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
Data Processing |
M |
|
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
Data Processing |
F |
You get this result:
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Examples: Appending Tables by Matching on Name and Type
When you Match on Name and Type, Monarch Data Prep Studio will append only tables with columns that have the same name and data type. This append follows the same logic as Match on Name, but with the added condition that the column types match.
Example 1
Consider the following tables to append.
Data Processing
|
Department |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID (text) |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID (text) |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
The result will be:
|
Department |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
Marketing |
April |
Anne |
|
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
|
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
|
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
|
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
|
6/4/2005 0:00 |
M |
In this example, the Emp ID column values of the Marketing and Accounting tables are not included because they do not match the data type of the Emp ID column in the Data Processing table.
Example 2
If you have the following tables:
Data Processing
|
Department |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID (text) |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Employee ID (number) |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
The result will be:
|
Department |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
Marketing |
April |
Anne |
|
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
|
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
|
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
|
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
|
6/4/2005 0:00 |
M |
In this example, the Emp ID column values of the Marketing and Accounting tables are not included because they do not match either the name or the data type of the Emp ID column in the Data Processing table.
Example: Appending Tables by Matching on Order
When you Match on Order, Monarch Data Prep Studio will append only tables with columns that match on position and ignore column names and data types.
Consider the following tables to append.
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID (number) |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID (text) |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
If:
-
The Department column of the Data Processing table has a different name, and
-
The Emp ID column of the Accounting table has a different data type (text instead of number)
The result will be:
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
In this example, all values of Emp ID from all tables are included since we are only matching on Order and Name.
Examples: Appending Tables by Matching on Order, Name, and Type
When you Match on Order, Name, and Type, Monarch Data Prep Studio will append only tables with columns that match on position, name, and type.
Example 1
Consider the following tables to append:
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
If the columns in all tables have matching data types, the Append Tables > Match on Order, Name, and Type result will be:
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
|
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
|
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
|
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
|
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
|
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
In this example, the Department column values of the Marketing and Accounting tables are not included because they do not match the name of the first column in the first table.
Monarch Data Prep Studio considers the first table as the basis for appending succeeding tables. Thus, if you append tables in a different order, i.e.:
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
You get this result instead:
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
|
|
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
|
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
|
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Example 2
Consider the following tables to append.
Data Processing
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
Marketing
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Marketing |
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
Marketing |
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
Accounting
|
Department |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Accounting |
Aldridge |
Jeff |
1592784 |
10/13/1995 0:00 |
M |
|
Accounting |
Daley |
William |
1587390 |
1/30/1993 0:00 |
M |
|
Accounting |
Georges |
William |
1596792 |
6/4/2005 0:00 |
M |
If:
-
The Department column of the Data Processing table has a different name, and
-
The Emp ID column of the Accounting table has a different data type (e.g., text instead of number)
The Append > Matching on Order, Name, and Type result will be:
|
Dept |
Last Name |
First Name |
Emp ID |
Date |
Gender |
|
Data Processing |
Bass |
Andrew |
1593211 |
3/18/1999 0:00 |
M |
|
Data Processing |
Bittner |
Herb |
1597596 |
3/18/2005 0:00 |
M |
|
Data Processing |
Bittner |
Martha |
1604193 |
12/18/2001 0:00 |
F |
|
|
April |
Anne |
1597429 |
10/23/1993 0:00 |
F |
|
|
Banning |
David |
1607768 |
12/18/2003 0:00 |
M |
|
|
Aldridge |
Jeff |
|
10/13/1995 0:00 |
M |
|
|
Daley |
William |
|
1/30/1993 0:00 |
M |
|
|
Georges |
William |
|
6/4/2005 0:00 |
M |
Related Links
© 2024 Altair Engineering Inc. All Rights Reserved.