topic How to get max value of date from 4 columns? in Talend Studio

How to get max value of date from 4 columns?

yashfreaky — Thu, 31 Jan 2019 09:21:19 GMT

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:

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

Re: How to get max value of date from 4 columns?

ankit7359 — Thu, 31 Jan 2019 09:34:59 GMT

Hi @yashfreaky ,

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

Thanks,

Ankit.

Re: How to get max value of date from 4 columns?

ThWabi — Thu, 31 Jan 2019 10:08:11 GMT

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

Re: How to get max value of date from 4 columns?

Anonymous — Thu, 31 Jan 2019 10:11:47 GMT

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 🙂