Column generator stage example

For the example you are going to generate an extra column for a data set containing a list of seventeenth-century inhabitants of Woodstock, Oxfordshire.

The extra column will contain a unique id for each row.

The columns for the data input to the Column Generator stage is as follows:

Table 1. Input data
Column name	SQL type	Length
DocumentID	VarChar	50
Year	VarChar	50
Orderno	Integer	10
Fname	VarChar	50
Sname	VarChar	50
Office	VarChar	50

You set the Column Generator properties to add an extra column called uniqueid to the data set as follows:

Column Method = Explicit
Column To Generate = uniqueid

The new column now appears on the Output page Mapping tab and can be mapped across to the output link (so it appears on the Output page Columns tab):

Shows the generated column on the Mapping tab — Figure 1. Mapping tab

In this example you select the uniqueid column on the Output page Columns tab, then choose Edit Row... from the shortcut menu. The Edit Column Meta Data dialog box appears and lets you specify more details about the data that will be generated for the new column. First you change the type from the default of char to integer. Because you are running the job in parallel, you want to ensure that the id you are generating will be unique across all partitions, to do this you set the initial value to the partition number (using the special value `part') and the increment to the number of partitions (using the special `partcount'):

When you run the job in parallel on a four-node system the stage will generate the uniqueid column for each row. Here are samples of partition 0 and partition 1 to show how the unique number is generated:

Table 2. Unique number generation for partition 0
uniqueid	DocumentID	Year	Orderno	Fname	Sname
0	WBR 96 folio 11	1617	110	Henry	Archer
4	WBR 96 folio 15	1619	115	Henry	Archer
8	WBR 96 folio 21	1622	117	Henry	Archer
12	WBR 96 folio 15	1619	95	John	Archer
16	WBR 96 folio 21	1622	82	John	Archer
20	WBR 96 folio 34	1627	95	John	Archer
24	WBR 96 folio 1-46	1662	125	Thomas	Archer
28	(court leet attendees)	1687	67	Thomas	Archer
32	Portmouth Book	1616	36	William	Archer
36	WBR 96 folio 11	1617	37	William	Archer
40	WBR 96 folio 1-46	1662	102	William	Ball
44	WBR 96 folio 11	1617	59	George	Barnsley
48	Portmouth Book	1616	35	John	Batt
52	Portmouth Book	1616	65	John	Batt
56	WBR 96 folio 11	1617	35	John	Batt
60	WBR 96 folio 15	1619	37	John	Batt
64	WBR 96 folio 15	1619	38	John	Batt
68	WBR 96 folio 15	1619	114	Gey	Becksley
72	WBR 96 folio 21	1622	116	Gey	Becksley
76	WBR 96 folio 34	1627	114	Guye	Becksley

Table 3. Unique number generation for partition 1
uniqueid	DocumentID	Year	Orderno	Fname	Sname
1	WBR 96 folio 21	1622	113	Marmad	?
5	WBR 96 folio 21	1622	68	Thomas	Asplene
9	(court leet attendees)	1687	83	Charles	Aston
13	WBR 96 folio 34	1627	107	Richard	Barnshooe
17	WBR 96 folio 34	1627	103	Walter	Bayless
21	(court leet attendees)	1687	3	Nicholas	Baynton
25	WBR 96 folio 21	1622	111	Robert	Belcher
29	WBR 96 folio 11	1617	102	Thomas	Belcher
33	WBR 96 folio 1-46	1662	24	Perrigrine	Bertie
37	WBR 96 folio 1-46	1662	16	John	Bignill
41	Portmouth Book	1616	16	Robert	Bignill
45	WBR 96 folio 1-46	1662	48	James	Blunden
49	(court leet attendees)	1687	66	William	Blunden
53	WBR 96 folio 1-46	1662	119	John	Borman
57	WBR 96 folio 11	1617	68	Henry	Bradshaw
61	WBR 96 folio 34	1627	88	William	Bradshaw
65	WBR 96 folio 21	1622	23	Robert	Brewce
69	WBR 96 folio 21	1622	21	Thomas	Brewce
73	WBR 96 folio 1-46	1662	84	Richard	Broffett
77	(court leet attendees)	1687	34	John	Brotherton