Skip to content

Field to Row Transposition

Some source files carry several amounts side by side on one line — a net amount and a tax amount, or a line total and a line-level shipping charge — where the target system wants each of them as a line of its own. This article turns columns into rows.

Two source rows, each with an Amount column shaded blue and a Tax column shaded pink, becoming four rows carrying a single LineAmount column, in which each value keeps the colour of the column it came from. The line number and item are repeated on every row produced from the row they came from.

It is worth doing when:

  • there are net and tax amount fields, and these must be imported as separate lines;
  • a line-level shipping charge has to be posted as its own line.

No single transform does this. What does it is four of them in a row, and the point of the recipe is the shape of the chain rather than any one setting. Read the Aggregate and Hierarchy transforms first; between them they do the actual work.

The chain

Six transforms in a row: an Excel Reader, two Hierarchy transforms, a Map, an Aggregate and a Flatten.

Transform What it is for
1 Excel Reader Reads the sample workbook
2 Hierarchy Splits the flat rows into a header and its lines
3 Hierarchy Adds a level below the line, which is where the transposition happens
4 Map Adds the empty field the transposed values will land in
5 Aggregate Writes one record per column being transposed
6 Flatten Folds that level back up, so each transposed value is a line

Steps 1 and 2 exist only to produce a header/detail dataset to work on. If your integration already has one — and most do, because a reader usually builds it — start at step 3.

1. The sample data

  1. Download the sample workbook and save it somewhere the IMan server can read.

  2. Add an Excel Reader, set File Path and File Name to where you saved it, and set Header Rows to 1.

    The Excel Reader Setup tab. Source is File, File System Windows, with the file path and the file name FieldToRowTranspositionExample.xlsx; under Options, Worksheet Id 0 and Header Rows 1.

  3. Press Refresh.

The workbook holds two rows, both belonging to order REF00123: line 1 for item A1-103/0 with an amount of 100 and tax of 15, and line 2 for item A1-401/0 with an amount of 230 and tax of 46. By the end there will be four.

2. Hierarchy: a header and its lines

  1. Close the reader, add a Hierarchy transform and open its Field Mapping tab.
  2. Leave Transaction Id to Hierarchise on Root, and press Edit to make the field list editable.

  3. Give ID and TranNo keys 1 and 2, and deselect LineNo, Item, Qty, Amount and Tax.

    The Hierarchy field grid on Root. ID and TranNo are ticked with keys 1 and 2, Date and SYS.INPUTFILE are ticked, and LineNo, Item, Qty, Amount and Tax are unticked.

  4. Press Save.

    Unticking Import asks you to confirm

    Saving a batch in which any field has been unticked raises a Remove Fields dialog listing them. That is the transform telling you those fields are about to leave it, and it has to be answered before anything else on the pane will respond.

  5. Type Detail into New Transaction Id and press the ▶ button beside it.

    The top of the Hierarchy Field Mapping tab: the Transaction Id to Hierarchise dropdown, the New Transaction Id box with its add button, and the hierarchy showing Root with Detail beneath it.

  6. Select Detail in the hierarchy, press Edit, give ID, TranNo and LineNo keys 1, 2 and 3, and deselect Date.

    The Hierarchy field grid on Detail. ID, TranNo and LineNo carry keys 1, 2 and 3; Date is unticked.

  7. Press Save, then Refresh. The data is now a header with its lines beneath it.

    The preview grid. One Root row for order REF00123, expanded to show two Detail rows, lines 1 and 2.

3. Hierarchy: a level below the line

This is the first step of the transposition proper. It creates a transaction that is a child of Detail, and everything that follows happens on that new level rather than on the line itself. Working a level down lets the Aggregate multiply records without multiplying lines.

Depending on your input you may be able to use a hierarchy that already exists. This example adds a second one.

  1. Add a Hierarchy transform, open Field Mapping, and set Transaction Id to Hierarchise to Detail.

    The second Hierarchy's Field Mapping tab, with Transaction Id to Hierarchise set to Detail and the hierarchy showing Root, Detail and SubDetail.

    Set this first

    Until Transaction Id to Hierarchise has a value the rest of the tab does nothing at all: the hierarchy renders empty, the field grid has no Import column, and New Transaction Id and its button accept input and produce nothing. Nothing is greyed out and nothing reports an error, so it reads as a broken pane rather than an unset field.

  2. Press Edit, deselect Amount and Tax, and give LineNo a key of 1.

    The field grid on Detail. LineNo carries key 1; Amount and Tax are unticked.

  3. Press Save, then create a transaction called SubDetail the same way as before.

  4. Select SubDetail, press Edit, deselect ID, TranNo, Qty and SYS.INPUTFILE, and give LineNo and Item keys 1 and 2.

    The field grid on SubDetail. LineNo and Item carry keys 1 and 2; ID, TranNo, Qty and SYS.INPUTFILE are unticked, leaving Amount and Tax imported.

  5. Press Save, then Refresh. There are now three levels, and the bottom one holds a single record per line.

    The preview grid expanded twice: Root, then Detail, then a SubDetail grid holding one record with LineNo 1, Item A1-103/0, Amount 100 and Tax 15.

4. Map: somewhere for the value to go

The Aggregate cannot write into a field that does not exist, so this step adds one. If you had two sets of columns to transpose you would add a field per set.

  1. Add a Map transform, open Field Mapping and change Current Transaction Id to SubDetail.

  2. Press Add, name the field LineAmount, and leave Evaluate String empty — the Aggregate fills it, not the Map.

    The Field Mapping dialog for LineAmount. New Field Name is LineAmount, Type is Text, Evaluate is unticked and Evaluate String is empty.

  3. Press the green tick to save.

    The Map field grid on SubDetail, with LineAmount added below LineNo, Item, Amount and Tax.

  4. Press Refresh. LineAmount now exists on the SubDetail level, empty.

    The preview expanded to SubDetail, which now has a LineAmount column with no value in it.

5. Aggregate: the transposition

This is where the work happens. The Aggregate adds two calc records to SubDetail, one per column being transposed. Each is a copy of the record it came from, with LineAmount set from a different source field — so one record carries the amount and the other carries the tax.

  1. Add an Aggregate transform, open Field Mapping and change Current Transaction Id to SubDetail.

  2. Press +, set Description to AmountLine, then edit the LineAmount row: tick Evaluate and set Evaluate String to %Amount.

    The AmountLine calc record. Description AmountLine, Record Inclusion Condition True, and a field grid in which LineAmount has Evaluate ticked and an Evaluate String of percent Amount.

  3. Press + again, set Description to TaxLine, and set the same field's Evaluate String to %Tax.

    The TaxLine calc record, identical but with an Evaluate String of percent Tax.

  4. Tick Delete Child Records. This removes the original SubDetail record, leaving only the two calc records — otherwise the line's own row survives alongside them with an empty LineAmount.

    The top of the Aggregate Field Mapping tab. Current Transaction Id is SubDetail, Delete Child Records is ticked, and the Calc Records dropdown shows AmountLine.

    Both of these belong to SubDetail

    Delete Child Records is per transaction, and so are the calc records. Both apply to whatever Current Transaction Id is showing when you set them. Ticked while the pane is on Root it deletes the entire dataset: the transform emits no rows, reports no error, and every transform upstream of it still previews correctly.

    Current Transaction Id is a view selector rather than saved state, so the pane always reopens on Root. Change it back to SubDetail before reading anything off this tab.

  5. Press Refresh and expand down to SubDetail. Each line now has two records where it had one, and LineAmount carries the amount on the first and the tax on the second.

    The preview expanded to SubDetail, showing two records for line 1: both with Amount 100 and Tax 15, but LineAmount 100 on the first and 15 on the second.

Why both records are made here

The obvious alternative is to copy Amount into LineAmount back in the Map transform, and have the Aggregate create only the TaxLine record. It works, and it is a little clumsy: the logic for what becomes a line is then spread across two transforms, and the Map's copy is doing something quite different from the field creation around it. Making both records in the Aggregate keeps the whole rule in one place, where it can be read at once and changed at once.

6. Flatten: back to one row per value

The SubDetail level has done its job. Flattening it away leaves the transposed values on the lines themselves.

  1. Add a Flatten transform, open Field Mapping and set Transaction Id to Flatten to Detail.

  2. Press Edit and deselect LineNo, Item, Amount and Tax from the SubDetail rows, leaving only LineAmount. Detail already carries a LineNo and an Item of its own, so the copies coming up from below are not wanted — and why the Flatten has renamed them SubDetail_LineNo and SubDetail_Item in New Name. It does that to whichever of two colliding fields it reaches second, so the shallower one keeps the plain name.

    The Flatten field grid. Rows are grouped by Import Tran Id; under SubDetail only LineAmount is ticked.

  3. Press Save, then Refresh, and expand the header row.

    The preview expanded to Detail. Four rows where there were two: lines 1 and 1 with LineAmount 100 and 15, then lines 2 and 2 with LineAmount 230 and 46.

Two source rows have become four, each carrying one value in one field. A writer downstream can now post the tax as a line without knowing it was ever a column.

Verified against IMan 6.1, September 2026.