Four Stars

Create rows from columns

Hi,

 

I want to get rows from some columns. In my TOS DI is nothing like unpivot.

My data is stored in an excel spreadsheet and the structure looks like this.

 

order_nr  | name | ali_1 | ali_2 | ali_3 | ali_4 | ali_4

-------------------------------------------------------------------

1234       | test    | 10      | 100  | 200

5678       | test-2 | 100    | 200  | 300 | 400

 

The output should be the following

1234-1 | test | 10

1234-2 | test | 100

1234-3 | test | 200

5678-1 | test-2| 100

5678-2 | test-2| 200

5678-3 | test-2| 300

5678-4 | test-2| 400

 

How can I achive this?

 

Best regards

Tags (2)
2 REPLIES
Seven Stars

Re: Create rows from columns

tsplitsrow would help in this case.
Seven Stars

Re: Create rows from columns

You can do this with a combination of tSplitRow and tFilterRow as follows:

 

Results.png

 

The tSplitRow has an output schema of just the three fields:

 

tSplitRowSchema.png

 

And is configured to produce a line for each of the value columns:

 

tSplitRow.png

 

You then just filter out the ones with empty values using  tFilterRow:

 

tFilterRow.png