Create rows from columns

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)
Eight Stars

Re: Create rows from columns

tsplitsrow would help in this case.
Eight 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

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog