need help on below scenario

Five Stars

need help on below scenario

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


Accepted Solutions
Employee

Re: need help on below scenario

Hi,

 

    I believe you are looking for the below solution.

 

image.png

 

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

image.png

 

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.

image.png

 

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.

image.png

The output is as shown below.

image.png

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 :-)


All Replies
Highlighted
Eleven Stars

Re: need help on below scenario

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 .

 

 

Regards
Abhishek KUMAR
Five Stars

Re: need help on below scenario

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

 

Employee

Re: need help on below scenario

Hi,

 

    I believe you are looking for the below solution.

 

image.png

 

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

image.png

 

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.

image.png

 

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.

image.png

The output is as shown below.

image.png

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 :-)

Eleven Stars

Re: need help on below scenario

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))

 

TalendImage.JPG 

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

Regards
Abhishek KUMAR

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog