topic Convert one row fieldset to multiple rows in Talend Studio

Convert one row fieldset to multiple rows

Anonymous — Sat, 16 Nov 2024 13:23:17 GMT

Hi all,
I was wondering if there is a more efficient way to convert a multiple fieldset from one source row into multiple rows for an output.
Example:
I have a row with the structure
key, value-a-1, value-a-2, value-a-3, value-b-1, value-b-2, value-b-3
and like to have an row (flow) like
key, value-a-1, value-b-1
key, value-a-2, value-b-2
key, value-a-3, value-b-3
My idea is at the moment to use a tMap to split the multiple fieldsets into three different flows for the output. As this is replicating quite some code I was wondering if there is a more efficient or elegant way.
Thx for your suggestions
Alex

Re: Convert one row fieldset to multiple rows

alevy — Thu, 17 Jun 2010 02:20:34 GMT

Use a lookup table for your tMap containing one column and three rows with the values "1", "2" and "3" but don't link the main input table to the lookup table. This will give you three output rows for each input row and you can map the relevant field value depending on the lookup value e.g. lookupFlow.columnName.equals("1")?mainFlow.valueA1:lookupFlow.columnName.equals("2")?mainFlow.valueA2:mainFlow.valueA3.
See https://community.talend.com/t5/Design-and-Development/resolved-Mapping-a-single-row-to-multiple-rows/td-p/113293 for a similar example.

Re: Convert one row fieldset to multiple rows

Anonymous — Mon, 21 Jun 2010 17:16:08 GMT

Dear Alevy,
Thanks this is a helpful solution and works.
Do you have a smart idea how to avoid having an if statement for more than 3 different fields?
My idea is to use Java reflection like this to call the fields dynamic. However I'm not sure how to integrate this in the tMap. It doesn't look very simple.
Regards

Re: Convert one row fieldset to multiple rows

alevy — Tue, 22 Jun 2010 00:02:03 GMT

I don't know Java so have no idea what reflection is. My suggestion above is the best I've come up with although I have seen other suggestions on the forum e.g. https://community.talend.com/t5/Design-and-Development/How-can-I-splitt-an-inputrow-into-twi-outputrows/td-p/64157.
Other ideas of mine but not really better:
-- Carry the lookup and main values through tMap as is and assign them to individual fields using a switch statement in tJavaRow.
-- Concatenate your fields in tMap as "value-a-1, value-b-1; value-a-2, value-b-2; value-a-3, value-b-3" then tNormalize on ";" and then tExtractDelimitedFields on "," (e.g. https://community.talend.com/t5/Design-and-Development/Integrating-Seperate-Columns-in-to-one-Column/td-p/61661 and https://community.talend.com/t5/Design-and-Development/resolved-Split-a-single-row-to-multiple-rows-CSV-File/td-p/109423).

Re: Convert one row fieldset to multiple rows

Anonymous — Tue, 22 Jun 2010 08:41:27 GMT

Dear Alevy,
First off - your first solution is perfect!
It works and does exactly what it is supposed to do.
I think the tJavaRow & switch is quite similar what we can achieve with the if statement. However the tJavaRow can organize a reflection style approach what I was looking for.
The reflection in Java allows you to dynamically access attributes of a class or methods. It is a bit similar like using PHP with
$fieldName ="value-a-" + lookupRow->code;
$value = $row->$fieldName;
In Java you need to do a work around using a method and then invoke the method instance. Its a bit more code but will work quite similar.
Thank's again for your help - your solution worked for me
Alex

Re: Convert one row fieldset to multiple rows

alevy — Sun, 12 Jun 2011 03:29:15 GMT

From v4.2.0, the tSplitRow component handles this very well.
And joined outputs in tMap can be even better since you can drag and drop. tSplitRow is a pain to use.