Hi Sorry if this is already answered somwhere but I cannot find it. I need to split one row into columns: RecordIDValue22523tests28029failures34035ignored401.003s41duration50100%51successful into this: testsfailuresignoreddurationsuccessful5001.003s100%

Rows to Columns

Hi Sorry if this is already answered somwhere but I cannot find it. I need to split one row into columns:

RecordID	Value
22	5
23	tests
28	0
29	failures
34	0
35	ignored
40	1.003s
41	duration
50	100%
51	successful

into this:

tests	failures	ignored	duration	successful
5	0	0	1.003s	100%

Workflow

Transformation

Accepted answers

BenMoss

Here is a solution but of course I have made several assumptions.

My guess is there is likely multiple instances which perhaps have a seperate ID column to indicate which instance they belong. In my example I have created two examples. One purely with the data you have and one with how to deal with it if you have multiple instances.

Ben

Workflow.yxmd

mbarone

Here's something based on the assumptions that (1) first record will always be the value, and each record there after will alternate between header and value, and (2) you want to go from 2 columns to however-many-header columns.

Step 1: Your incoming records

Step 2: Issue a record ID

Step 3: Make even records headers, and odd records values:

Step 4:

Assign header-value pairs the SAME number. Doesn't matter what the numbers are, as long as the alternating headers/values have the same number:

Step 5: First of a double cross-tab. Get the header/values lined up correctly:

Step 6: Second of the double cross tab: Stretch out the data into one row:

That should do it.

All comments

mbarone

Since both your headers and values are in the same column, and can be a mix between numbers and text, I see no way of doing this UNLESS you have a hard and fast "rule" to determine which cells in that column are headers and which are the values that go under the headers.

Is there any such rule you? In your example, it looks like the odd-numbered record IDs are the headers and the even-numbered record IDs are the values. Is that always going to be the case? It also appears that every other row is a header/value (i.e., they alternate)? Is that always the case? Will the VERY FIRST row ALWAYS be a value and then alternate from there on out as header/value, header/value?

If there is a rule, then it can be done.

Or are you doing the opposite? I can't tell from the phrasing of the question versus the examples you gave.

dlesny

Hi, thank you for your help

yes that will always be the case for both, I am transforming HTML cucumber report, the id's are just leftovers from my previous transformations, can be removed or input again from one.

BenMoss

Here is a solution but of course I have made several assumptions.

Ben

Workflow.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335