Loop job over date range set in global variable

[DavidETL]

Hi All,

I'm trying to get my job to loop over a date range, and pass the date it is looping for to a tmap further down the chain so the date it is looping for can be captured in a database.

I have two variables set as global variables.

"varStartDate" = TalendDate.formatDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.getCurrentDate(), -7, "dd"));
"varEndDate" = TalendDate.formatDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.getCurrentDate(), 0, "dd"))

I then have a tJava component:

java.util.Date start_date=TalendDate.parseDate("yyyy-MM-dd", (String)globalMap.get("varStartDate"));
java.util.Date end_date=TalendDate.parseDate("yyyy-MM-dd", (String)globalMap.get("varEndDate"));
long l=TalendDate.diffDate(end_date, start_date);
globalMap.put("iterate", l);

I followed this tutorial but having no luck. 

https://community.talend.com/t5/Design-and-Development/resolved-Is-it-possible-to-use-tloop-to-loop-...

is there a simpler approach?

[Anonymous]

I suspect the issue is that you are just getting the date difference in days, but doing anything with it. Run this code in a tJava and see if this is what you want...

java.util.Date start_date=TalendDate.parseDate("yyyy-MM-dd", (String)globalMap.get("varStartDate"));
java.util.Date end_date=TalendDate.parseDate("yyyy-MM-dd", (String)globalMap.get("varEndDate"));
l=TalendDate.diffDate(end_date, start_date);

Date new_start_date;

for(int i=0; i<=l; i++){
      new_start_date = TalendDate.addDate(start_date, i, "dd");
      System.out.println(TalendDate.formatDate("yyyy-MM-dd", new_start_date));
}

What I am doing here is appending days to the start_date in a loop based on the number of days difference. I think you need to slightly modify your job to follow this sort of logic.

[DavidETL · Accepted solution]

Thanks for the reply. When I use the code provided it asks for input.

Ideally what I'd like is to create a list of dates, and feed it into a tFlowToIterate component. Currently, I have it working off an SQL view but want to switch it, so a user enters a start date and end date into a tsmgbox, which is then passed to a global var (for later use), and then the dates between the list and moved to the TFlowToIterate.

is there a way to produce a virtual table of dates between a range in Talend I can pass to a tflowtoIterate component?

[Anonymous]

The above code should not ask you for input. My assumption was that you would already have your start and end date globalMap variables populated. If not, you will need to do this. Once that is done, if you run the code it will produce you a list of dates in the output window.

[DavidETL]

thank you, all good

[Anonymous]

@rhall 
I want to pass a date variable to a sql query, and write the query result into a flat file. So there will be a file for each date variable passed. I found this post helpful, at least I could get a list of dates within the required range. Here is the test I did using the codes you and others provided:

 

But then what's the next? How could I pass each and every new_start_date into my child job

(which would create the flat file for each date passed in)? 

Thanks!