Tall to Wide and Wide to Tall

Tall to Wide

The Tall to Wide tool is the logical complement of Wide to Tall. It accepts records in a "tall" form, and converts them to a "wide" form. Given all adjacent records with the same ID value, it appends up to N sets of widened field values, numbering each set of field names so as to ensure uniqueness. Because it collapses multiple records into one, it must choose the record from which the “spectator” fields will be copied, and it always chooses the first record of the group.

For example, if the input data is:

ID	FIRSTNAME	LASTNAME	ADDRESS
1	Joe	Jones	123 Main
1	Joe	Jones	PO Box 80
1	Joseph	Jones	80 S Hwy 3
2	MaryAnn	Smith	456 Grant St
2	MaryAnne	Smithe	N/A
2	Mary Ann	Smyth	18 1st Ave

And you configured the Tall to Wide tool to widen the Address field to three fields and omit null records, the output would be:

ID	FIRSTNAME	LASTNAME	ADDRESS1	ADDRESS2	ADDRESS3
1	Joe	Jones	123 Main	PO Box 80	80 S Hwy 3
2	MaryAnn	Smith	456 Grant St	18 1st Ave	N/A

Wide to Tall tool configuration parameters

The Wide to Tall tool has a single set of configuration parameters in addition to the standard execution options.

Parameter	Description
ID field	The input field containing the record ID.
Number of wide fields	The number of sets (between 1 and 1000) of wide output fields to create.
Start numbering at	Specifies the starting number to be appending to wide fields.
Fields like NAME01 instead of NAME1	If selected, output wide fields will have a leading zero appended to the wide field name.
Skip nulls	If selected, skip records for which all of the to-be-widened field values are blank. However, if all records in a group have blank wide-field values, the first record in the group is output.
Default numbers to zero instead of null	If selected, sets missing numeric field values to zero.
Fields	Use the Fields grid to select the tall fields to be widened.

Configure the Tall to Wide tool

Select the Tall to Wide tool.
Go to the Configuration tab on the Properties pane.
Specify the ID field, and values for Number of wide fields and Start numbering at.
Optionally, select Fields like NAME01 instead of NAME1.
Select Skip nulls to skip records for which all of the to-be-widened field values are blank.
Select Default numbers to zero instead of null if some numeric field values may be missing, and you want those to be zero.
Use the grid to Select tall fields to widen.
Optionally, go to the Execution tab, and then set Web service options.

The ID field values need not be unique, sequential, or numeric.
Only adjacent runs of the same ID field value are counted as the same record. If you want noncontiguous sets to be treated globally, sort by ID first.
Missing values will result in either Null or zero values on the output record, depending on the setting of the Default numbers to zero instead of null option.

Wide to Tall

The Wide to Tall tool is the logical complement of Tall to Wide. It accepts records in a "wide" form, and converts them to a "tall" form. Sets of wide fields must all have the same prefix, differing only by a sequential number. You need only specify the first exemplar field. The tool will infer that all similar fields are included.

For example, if the input data is:

ID	NAME	ADDR1	ZIP1	ADDR2	ZIP2
1	Joe Jones	123 Main	19204	PO Box 80	35976
2	Mary Smith	456 Grant St	57892	456 Grant St	57892
3	Ralph Gomez	N/A	N/A	17 Hwy 6	34577

And you configured the Wide to Tall tool to convert the ADDR and ZIP fields, omitting nulls, the output would be:

ID	NAME	ADDR	ZIP
1	Joe Jones	123 Main	19204
1	Joe Jones	PO Box 80	35976
2	Mary Smith	456 Grant St	57892
2	Mary Smith	456 Grant St	57892
3	Ralph Gomez	17 Hwy 6	34577

This tool works only for input data where the input "wide" fields are consistently named. For example, if the Wide to Tall tool has the following input fields:

NAME,ADDR1,ADDR2,ADDR3,PHONE1,PHONE2,PHONE3

You can select both ADDR1 and PHONE1 fields, and the tool will automatically figure out that you want to merge the six input fields into two fields—ADDR and PHONE—and create three tall records for each wide input record.

But the prefixes must be identical, the numbering must be sequential, and the numbering must be consistent. Consider the following sets of input fields:

NAME,ADDR,ADDR1,ADDR2,PHONE1,PHONE2,PHONE3

NAME,ADDR1,ADDR2,ADDR3,PHONE2,PHONE3,PHONE4

In both cases, you can process either ADDR or PHONE, but not both at the same time. In the first set of input fields, ADDR has no number but PHONE does. In the second set, the numbering differs, with ADDR starting at 1 and PHONE at 2.

Wide to Tall tool configuration parameters

The Wide to Tall tool has a single set of configuration parameters in addition to the standard execution options.

Parameter	Description
ID field	The input field containing the record ID.
Skip nulls	If selected, output one record with blank values if all records in a group have blank wide-field values. This prevents losing the records entirely.
Fields	Use the Fields grid to Select one field to represent each set of wide fields.

Configure the Wide to Tall tool

Select the Wide to Tall tool.
Go to the Configuration tab on the Properties pane.
Specify the ID field.
Select Skip nulls to output one record with blank values if all records in a group have blank wide-field values.
Use the grid to Select one field to represent each set of wide fields.
Optionally, go to the Execution tab, and then set Web service options.

The ID field values need not be unique, sequential, or numeric.
Only adjacent runs of the same ID field value are counted as the same record. If you want noncontiguous sets to be treated globally, sort by ID first.