How to get max value of date from 4 columns?

[yashfreaky]

Hello Talend Developers,

How to get max value of date from 4 columns? and if visit2 have max date then that row have to be deleted.

i/p:

id|name|visit1|visit2|visit3|visit4

1|yash|15-01-2019|10-05-2019|31-01-2019|02-01-2019

2|avin|30-01-2019|10-01-2019|05-01-1019|03-01-2019

3|seth|10-01-2019|15-01-2019|09-01-2019||02-01-2019

o/p:

id|name|latestvisit

1|yash|31-01-2019

2|avin|30-01-2019

Please let me know how to get that o/p.

Thanks and Regards

Yashwanth Basetty

[ankit7359]

Hi @yashfreaky ,

you can use TalendDate.compareDate method to compare the dates.

Thanks,

Ankit.

[ThWabi · Accepted solution]

Hello Yashwanth,

here is one solution:

tMap

I hope it's readable. 😉

Compare visit2 last, because then you can determine if it is the maximum date and filter on it.

Best regards,

Thomas

[Anonymous]

Hi,

    Please try below logic.

The expression filter will remove any records where visit2 is maximum value. The expression in visit column will pick the maximum among other three values.

I have written the logic quickly. So I would suggest you to thoroughly check all combinations and if there are any issues, please make appropriate changes.

tMap

Expression Condition is as below.

TalendDate.compareDate(row1.visit2,row1.visit1,"dd-MM-yyyy")>=0?   (TalendDate.compareDate(row1.visit2,row1.visit3,"dd-MM-yyyy")>=0?
    (TalendDate.compareDate(row1.visit2,row1.visit4,"dd-MM-yyyy")>=0?false :true) :true):true

The condition to be used in column is as below.

TalendDate.compareDate(row1.visit1,row1.visit3,"dd-MM-yyyy")>=0?
    (TalendDate.compareDate(row1.visit1,row1.visit4,"dd-MM-yyyy")>=0?row1.visit1:row1.visit4)
    :(TalendDate.compareDate(row1.visit3,row1.visit4,"dd-MM-yyyy")>=0?row1.visit3:row1.visit4) 

Hope I have resolved your query. Please spare a second to mark the topic as resolved 🙂

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 🙂