topic Re: [resolved] Explode one row into many rows in Talend Studio

[resolved] Explode one row into many rows

_AnonymousUser — Sat, 16 Nov 2024 13:24:08 GMT

Hi...
Thanks in advance for your help.
I have a requirement like.. I need to split one row into multiple rows based on the days between the start and end date.
Example: Source Table
Name, Value, Start_Date, End_Date
A, 20, 01-Jan-2010, 04-Jan-2010
B, 10, 20-Jan-2010, 21-Jan-2010
Target Table
Name, Value, Date
A, 5, 01-Jan-2010
A, 5, 02-Jan-2010
A, 5, 03-Jan-2010
A, 5, 04-Jan-2010
B, 5, 20-Jan-2010
b, 5, 21-Jan-2010
Can any one pls help me how to get this done using Talend?
Thanks
sanguine

Re: [resolved] Explode one row into many rows

alevy — Wed, 09 Jun 2010 05:32:08 GMT

Nice one!
You need the following:
--Main--> tFlowToIterate --Iterate--> tJava --Iterate--> tLoop --Iterate--> tIterateToFlow --Main-->
In tFlowToIterate, assign each of the field values to keys: "Name", "Value", "StartDate", "EndDate".
In tJava,

Long lngPeriod = TalendDate.diffDateIgnoreDST((java.util.Date)globalMap.get("EndDate"),(java.util.Date)globalMap.get("StartDate"),"dd")+1;
Integer intPeriod = lngPeriod.intValue();
globalMap.put("Period",intPeriod);
globalMap.put("DayValue",(Integer)globalMap.get("Value")/intPeriod);

tLoop from 1 to (Integer)globalMap.get("Period") in steps of 1.
tIterateToFlow:
(String)globalMap.get("Name")
(Integer)globalMap.get("DayValue")
TalendDate.addDate((java.util.Date)globalMap.get("StartDate"),(Integer)globalMap.get("tLoop_2_CURRENT_ITERATION")-1,"dd")
Voila!

Re: [resolved] Explode one row into many rows

_AnonymousUser — Fri, 11 Jun 2010 15:23:43 GMT

Thank you very much Alvey...i tried the way you said...its working perfect.
Once again thank you
sanguine

Re: [resolved] Explode one row into many rows

alevy — Wed, 20 Oct 2010 01:50:06 GMT

This could probably be achieved more efficiently with an approach similar to https://community.talend.com/t5/Design-and-Development/How-to-create-multiple-rows-out-from-a-row-input/td-p/68500.