[resolved] Explode one row into many rows

[_AnonymousUser]

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

[alevy · Accepted solution]

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!

[_AnonymousUser]

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

[alevy]

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-in....