need help on below scenario

[Anonymous]

I have input data Like below , the requirement is to calculate the count of date which are more than 9 months for each ID

ID|Date
32145|04-03-2015
32145|09-02-2016
32145|12-11-2016
32145|05-03-2017
32145|23-12-2017

32172|05-03-2015
32172|04-05-2015
32172|03-02-2016
32172|04-12-2016
32172|03-05-2017

Output data should be looks like this

ID|Date|Count
32145|23-12-2017|3
32172|04-12-2016|2

[akumar2301]

Hello 

Not able to understand your expected result.

ID|Date|Count
32145|23-12-2017|3
32172|04-12-2016|2

Please explain the logic for Date and Count Here .

[Anonymous]

ok see for below ID 32145

already date are in ascending order the requirement is to take the diff of date like "04-03-2015-09-02-2016" which greater than 9 months then count=1 and again need to take the diff of  "09-02-2016 - 12-11-2016 " which is also greater than 9 months so count will increment to 2 (count =2) now take the same diff to next date "12-11-2016 - 05-03-2017" which is not greater than 9 months so count is same here i.e (count =2) , now for next date "05-03-2017-23-12-2017"  diff is greater than 9 months so count will increment to 3(count=3) and need to take the last diff date . this all for one group ID 32145 like this need to do all ID's . I just provided here 2 sample record

32145|04-03-2015
32145|09-02-2016
32145|12-11-2016
32145|05-03-2017
32145|23-12-2017

Hope this cleared you requirement 

Thanks,

Dhanraj

[Anonymous · Accepted solution]

Hi,

    I believe you are looking for the below solution.

In the first subjob, I will add a numeric sequence to identify the number of rows for each id (assuming the values are coming in sorted fashion).

The data will be passed to a hash (or file if you want) and the same hash will be read from both main and lookup flows. In the tmap, I will map the previous record with next record by joining based on sequence number and pick the records where the month difference is >=9.

The expression filter is as shown below.

TalendDate.diffDate(row2.input_date,row3.input_date,"MM") >=9

Then I will aggregate the output using taggregaterow as shown below.

The output is as shown below.

Hope I have answered your query 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[akumar2301]

Another way to implement is using tMap functionality to memories prev record information.

( Solution is work assuming your Input to tmap is sorted for ID and Date (Ascending))

 

Result

32145|04-03-2015|0
32145|09-02-2016|1
32145|12-11-2016|2
32145|05-03-2017|2
32145|23-12-2017|3
32172|05-03-2015|0
32172|04-05-2015|0
32172|03-02-2016|1
32172|04-12-2016|2
32172|03-05-2017|2