topic Re: need help on below scenario in Talend Studio

need help on below scenario

Anonymous — Tue, 12 Feb 2019 10:44:42 GMT

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

Re: need help on below scenario

akumar2301 — Tue, 12 Feb 2019 11:30:04 GMT

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 .

Re: need help on below scenario

Anonymous — Tue, 12 Feb 2019 11:45:37 GMT

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

Re: need help on below scenario

Anonymous — Tue, 12 Feb 2019 11:50:32 GMT

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 🙂

Re: need help on below scenario

akumar2301 — Tue, 12 Feb 2019 12:34:40 GMT

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