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 |
|
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 |
|
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:
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, and then 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 |
|
| 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:
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, and then 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.