|
Appending Tables from Prep Data
From the Prep Data Window, you can append tables by selecting them from the Table List (use Ctrl+click to select more than one table), and then selecting an Append operation from the context menu.
Note that:
-
Different options will produce different results.
-
In some cases, the order in which you select tables to append will affect the result.
Appending Tables using Strict Append
When you use Strict Append, all columns on all tables must match on Order, Name, and Type.
If one of these conditions do not match (e.g., one column on one table does not have the same name), the append operation will fail and Data Prep Studio will display an error message.
To append tables:
-
In the Prep Data window table list. select the tables you want to append. (Ctrl+click on the tables to select more than one table.)
-
Right click and select Append Tables > Strict Append from the context menu.
Example 1
If you have the following tables:
Data Processing Table
|
Department |
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 Table
|
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 Table
|
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 |
This will be the result of a strict append:
|
Department |
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 |
Example 2
If you have the following tables:
Data Processing Table
|
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 Table
|
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 Table
|
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 |
In this example, strict append will fail and display an error message because one of the conditions is not met. One column of the Data Processing table has a different name from the other tables (Dept vs. Deparrtment)
Appending Tables by matching on Order, Name, and type
When you Match on Order, Name, and Type, Data Prep Studio will append only tables with columns that match on position, name, and type. Unlike a Strict Append, Data Prep Studio will not fail outright, but will instead append matching columns from other tables.
To append tables:
-
In the Prep Data window table list, select the tables you want to append. (Ctrl+click on the tables to select more than one table.)
-
Right click and select Append Tables > Match Column on Order, Name, and Type from the context menu.
Example 1
Consider the following tables to append:
Data Processing Table
|
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 Table
|
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 Table
|
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 columns in all tables have matching data, types, 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.
Note that Data Prep Studio will consider the first table as the basis for appending succeeding tables. If you append tables in a different order, i.e.:
Marketing Table
|
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 Table
|
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 Table
|
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 will 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 Table
|
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 Table
|
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 Table
|
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
-
The Emp ID columns of the Accounting table has a different data type (text instead of number)
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 |
Appending Tables by matching on Order and Name
When you match on Order and Name, Data Prep Studio will append only tables with columns that match on position and name. Data Prep Studio will ignore data types.
To append tables:
-
In the Prep Data window table list, select the tables you want to append. (Ctrl+click on the tables to select more than one table.)
-
Right click and select Append Tables > Match Column on Order and Name from the context menu.
Example
Consider the following tables to append:
Data Processing Table
|
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 Table
|
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 Table
|
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 (text instead of number),
Append > Matching on Order and Name 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, all values of Emp ID from all tables are included since we are only matching on Order and Name.
Appending Tables by matching on Order
When you match on Order, Data Prep Studio will append only tables with columns that match on position. Data Prep Studio will ignore names and data types.
To append tables:
-
In the Prep Data window table list, select the tables you want to append. (Ctrl+click on the tables to select more than one table.)
-
Right click and select Append Tables > Match Column on Order and Name from the context menu.
Example
Consider the following tables to append:
Data Processing Table
|
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 Table
|
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 Table
|
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 (text instead of number),
Append > Matching on Order 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.