Search code examples
talend

How to do dynamic date range iteration in Talend?

I have MinLoginTime and MaxLoginTime stored in 2 globalmap variables:

globalMap.put("MinLoginTime","2017-10-24") //ignore the datetime format, but it a date
globalMap.put("MaxLoginTime","2018-04-26")

I want to put month wise iteration and fetch records. i.e. Here we see there are 7 months in example: 10,11,12,1,2,3,4

I want to generate these kind of dates:

FromDate    ToDate
2017-10-01  2017-10-31
2017-11-01  2017-11-30
2017-12-01  2017-12-31
...
2018-04-01  2018-04-30

Then, need to iterate over each of these rows and do something (lets use tLog for now)

Could someone please help as to what Talend components can be used here for generating date ranges, where to store them and how to iterate them to do something?

Solution

  • You can achieve this pretty easily using a combination of Talend components and some Java code. Talend has a good collection of date manipulation functions.

    enter image description here

    First, store your global variable dates as Date type.

    globalMap.put("MinLoginTime", TalendDate.parseDate("yyyy-MM-dd", "2017-10-24"))

    Then tLoop_1 loops on all the months between your min and max dates. This code gets the number of months between the 2 dates :

    TalendDate.diffDate((Date)globalMap.get("MaxLoginTime"),(Date)globalMap.get("MinLoginTime"),"MM")

    tJava_3 just stores the date of the current iteration in a CURRENT_DATE global variable. It is the sum of the min date and the current iteration value (from 0 to N months).

    globalMap.put("CURRENT_DATE", TalendDate.addDate((Date)globalMap.get("MinLoginTime"), (Integer)globalMap.get("tLoop_1_CURRENT_VALUE"), "MM"))

    tFixedFlowInput_1 defines 2 Date columns: FromDate and ToDate in order to get the first and last day of the current iteration's month respectively.

    TalendDate.getFirstDayOfMonth((Date)globalMap.get("CURRENT_DATE"))
TalendDate.getLastDayOfMonth((Date)globalMap.get("CURRENT_DATE"))

    Check TalendDate class reference for all date manipulation methods.