Best Of

Currently this isn't an option with the HTML table, I'd recommend adding this as an idea to the idea exchange.

I follow. Currently, your columns are something like:

• Order Number
• Schedule Date 1
• Completed Date 1
• Status 1
• Schedule Date 2
• Completed Date 2
• Status 2
• ….
• Schedule Date 10
• Completed Date 10
• Status 10

You want to unpivot so your columns are:

• Order Number
• Stage Number
• Schedule Date
• Completed Date
• Status

If un-pivot is giving you trouble, it wouldn't be that hard to do this manually (and maybe more computationally efficient? Not sure):

• Select columns: Order Number, Schedule Date 1, Completed Date 1, Status 1; rename without 1 (if that's part of the column name)
• Add a constant with the stage number
• Do these two steps for each of the 10 groups of columns. You could select the two blocks and copy-paste, changing just which columns and stage.
• Append the 10 stages

Alternatively, you could do the dynamic unpivot once, selecting the Order ID as not pivoted, and something like "Metric" to store the column names and "Value" to store the values, so your three columns are:

• Order ID
• Metric (Schedule Date 1/Completed Date 1/Status 1…Schedule Date 10/Complete Date 10/Status 10)
• Value

Then you could use a formula tile or split tile, to split the Metric name and stage, so your four columns are:

• Order ID
• Stage (1, 2, …10)
• Metric (Schedule Date, Completed Date, Status)
• Value

Then you could split that into three datasets using filter blocks: filter for metric=Schedule Date, metric=Completed Date, metric=Status)

Then you could join those three filter tiles together using Order ID and Stage as keys, renaming the "Value" column to the metric (Schedule Date, Completed Date, Status)

I had a similar ETL for a dataset with dozens of columns in pairs like "Question 1 TEXT", "Question 2 VALUE", "Question 3 TEXT", "Question 3 VALUE". I used a dynamic unpivot, then filtered for just the rows with TEXT and the rows with VALUE, then rejoined them, so I basically three columns; ID, TEXT, VALUE. It didn't take very long to run on a pretty big dataset.

EDIT: Marcel's answer of using the Pivot after the dynamic un-pivot and split makes more sense than filter+join. I'd be curious to see if Marcel's method or the manual method I started this answer with runs faster. The advantage of the dynamic-unpivot vs. manual appends is that it would be dynamic to different numbers of stages. You could have 1 stage or 50, and the same method works.

@robdmitchell There are a couple different ways you could do this in MagicETL:

• Date Operations Tile: Use the Difference between dates operation, with hours as the unit of measurement
• Formula Tile: Add a field with the following formula: DATEDIFF(`DateTimeOff`,`DateTimeOn`)*24 + TIME_TO_SEC(TIMEDIFF(`DateTimeOff`,`DateTimeOn`))/(60*60)

Disregard this question, found the answer in another post