How to Identify the Most Recent Date in a Column and Use the date to Output a seperate column in a tMap Expression

[Anonymous]

Hello Talend Experts,

I need to First convert my String Data Type in this format MM/dd/YYYY into a Date Format and then find the most recent date in a column based off a unique ID in another column and output a different column in the Final Output in tMap

I have records of phone numbers and every time a phone number gets changed it creates a new row with the modify date. So I'm only trying to grab the most recent phone number change to that unique ID.

I can format a String Data Type into a Date format I'm not worried about that I'm trying to find out the logic of grabbing the most recent date in a column and outputting a different column based on a unique ID.

I also have an additional Column in the input file that tells you the type of phone it is whether it's "Home" "Buisness" So I would be separating the Home and Business Number in two separate Output Columns in my tMap. So it would be grab the Most Recent Home Phone Number based off the Most Recent Date to the current date.

Any help would be greatly appreciated 

[akumar2301]

1st sort your input on Unique ID and change date ( descending )

use tAggregatedsortedrow 

group by all key columns ,

in function , use Aggregation function "First" for rest of comumns. 

[Anonymous]

Hi @uganesh 

Thanks for your reply

I am having some issues with my tSortRow and tSortAggregateRow Components

So My Date in my Input File is in this Format

 which is "MM/dd/yyyy HH:mm aa" I'm pretty sure

Input File ---> tSortRow ---> tAggregateSortedRow ---> tMap is my component sequence of the job

Whenever my job starts running my console log just spits out these outputs

Couldn't parse value for column 'Email_Addresses_Date_changed' in 'row7', value is '9/15/2018 02:43 PM'. Details: java.lang.RuntimeException: Unparseable date: "9/15/2018 02:43 PM"

Now in my Schema of my Input File my Date Changed Column has Type: Date and Date Pattern is "MM/dd/yyyy HH:mm aa". So I'm not sure why my console can't handle these dates. Does the Date need to be in a certain format for tSortRow to work?

Here is how my tSortRow Component is setup for Email Address Date Changed

Also One More Question If I have two Phone Number Fields For Mobile and Home in my Output how would I make sure talend grabs the second most recent date change(PhoneNumber) in the second column. I'm not looking to duplicate this data. My current syntax is for the First Phone Field is

"Mobile".equalsIgnoreCase(row5.Phones_Phone_type) && "True".equalsIgnoreCase(row5.Phones_Primary_phone_number)? row5.Phones_Number : "" 

How would I adjust this Code so the Output grabs the second most recent date phone number for the unique id and not the same one?

Thanks for the help!

-Andrew