topic Re: Iterate through columns and create one line per column in Talend Studio

Iterate through columns and create one line per column

_AnonymousUser — Fri, 03 May 2013 15:31:05 GMT

The title is a bit confusing
I want to know if it is possible to iterate through the column of a schema and for each pair of column / value create an input line (for a table for exemple)
For exemple, let's say I have a file and the columns are (the schema) : "id, name, price_2013, price_2012, price_2011, ..., price_1990".
And the targeted schema is : Table Object with column id and name, Table Prices with column id_object, year, price
So if in my file I have "3, foo, 12, 14, 11, ..., 20" I should end with the table Object that have one line (id => 3 and name => foo) and as many line for the table Prices as the number of price_XXXX
(line 1 : id_object => 3, year => 2013, price => 12 ; line 2 : id_object => 3, year => 2012, price => 14 ; ...)
The table object is ok I already created it and import the id and name but I want to find a way to iterate through the columns "price_XXXX" and create one line per column for the table "Prices"
I saw the iterate link but I don't know how to correctly use it to do what I want to do.
If you have any proposition I will be happy to read it

Re: Iterate through columns and create one line per column

Anonymous — Fri, 03 May 2013 18:32:41 GMT

Hi Clément_d,
I know two ways to achieve your goal. One is to use component tSplitRow and setup the component so that it will do this split in the fields for you.
This solution is hardcoded, I mean, you need manually define year by year how the split must work.
Please check the attached images to see an example.

Re: Iterate through columns and create one line per column

Anonymous — Fri, 03 May 2013 18:36:25 GMT

The other solution involves more code.
Using a tFlowToIterate we can store the row values in the globalMap and then using a tJavaFlex we loop through all the years and get the values.
Please check the attached images to understand.

Re: Iterate through columns and create one line per column

_AnonymousUser — Fri, 03 May 2013 18:58:07 GMT

Thank you very much,
That is exactly what I was looking for

Re: Iterate through columns and create one line per column

Anonymous — Fri, 03 May 2013 23:18:20 GMT

If you have a lot of columns the mentioned approach are very configuration intensive.
In Talend Exchange exists a component tNormalizeSchema which can setup a scenario like, normalize the next 100 fields with are occurrence 20 times.

Re: Iterate through columns and create one line per column

Anonymous — Wed, 23 Oct 2013 19:21:13 GMT

If you have a lot of columns the mentioned approach are very configuration intensive.
In Talend Exchange exists a component tNormalizeSchema which can setup a scenario like, normalize the next 100 fields with are occurrence 20 times.

This component works but this approach seems fine if you have a defined # of columns. Unfortunately doesn't seem to work where the number of columns will vary from row to row.