We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
community Alteryx IO MyAlteryx
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Get Max date of a column

Saravanan13
8 - Asteroid
‎06-08-2023 11:17 AM

Hello all,

 

I want to get  max date of each Cust ID from the  End date column and the same needs to be repeated for each rows and also added in a  separate column. Can anyone assist on this

 

Input - 

 

Cust IDstageStatusStatus Start DateDays in StatusHOURSCountEND_DATE
4689 Request ReceivedInProgress09-May-20230 Days-15/9/2023
4689 Request ReceivedInProgress09-May-20230 Days-25/9/2023
7901Account OpenCompleted16-Feb-20230 Days-32/16/2023
7901CompleteCompleted16-Feb-20230 Days-42/16/2023
7901Documents Out to ClientCompleted16-Feb-20230 Days-52/16/2023
7901Documents Out to ClientInProgress16-Feb-20230 Days-62/16/2023
7901ApprovalCompleted16-Feb-20230 Days-72/16/2023
7901ApprovalCompleted16-Feb-20230 Days-82/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-92/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-102/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-112/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-122/14/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-132/14/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-142/15/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-152/15/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-162/15/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-172/16/2023
6504Account OpenCompleted22-Mar-20230 Days-183/22/2023
6504CompleteCompleted22-Mar-20230 Days-193/22/2023
6504ApprovalCompleted22-Mar-20230 Days-203/22/2023
6504ApprovalCompleted22-Mar-20230 Days-213/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-223/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-233/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-243/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-253/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-263/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-273/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-283/22/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-293/21/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-303/21/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-313/22/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-323/21/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-333/21/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-343/21/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-353/21/2023
6504ResubmitCompleted22-Mar-20230 Days-363/22/2023
6504TaxCompleted22-Mar-20230 Days-373/22/2023
6504TaxCompleted22-Mar-20230 Days-383/22/2023
6504TaxCompleted22-Mar-20230 Days-393/22/2023
3333Account OpenCompleted11-Apr-20230 Days-404/11/2023
3333CompleteCompleted11-Apr-20230 Days-414/11/2023
3333ApprovalCompleted11-Apr-20230 Days-424/11/2023
3333ApprovalCompleted11-Apr-20230 Days-434/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-444/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-454/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-464/11/2023
3333Tax ApprovalCompleted11-Apr-20230 Days-474/11/2023
3333Tax ApprovalCompleted11-Apr-20230 Days-484/11/2023
3333 Request ReceivedInProgress10-Apr-20230 Days-494/10/2023
3333 Request ReceivedInProgress10-Apr-20230 Days-504/10/2023

 

Output -

 

Cust IDStageStatusStatus Start DateDays in StatusHOURSCountEND_DATEMax date
4689 Request ReceivedInProgress09-May-20230 Days-15/9/20235/9/2023
4689 Request ReceivedInProgress09-May-20230 Days-25/9/20235/9/2023
7901Account OpenCompleted16-Feb-20230 Days-32/16/20232/16/2023
7901CompleteCompleted16-Feb-20230 Days-42/16/20232/16/2023
7901Documents Out to ClientCompleted16-Feb-20230 Days-52/16/20232/16/2023
7901Documents Out to ClientInProgress16-Feb-20230 Days-62/16/20232/16/2023
7901ApprovalCompleted16-Feb-20230 Days-72/16/20232/16/2023
7901ApprovalCompleted16-Feb-20230 Days-82/16/20232/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-92/16/20232/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-102/16/20232/16/2023
7901Pending System DistributionCompleted16-Feb-20230 Days-112/16/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-122/14/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-132/14/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-142/15/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-152/15/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-162/15/20232/16/2023
7901 Request ReceivedInProgress14-Feb-20230 Days-172/16/20232/16/2023
6504Account OpenCompleted22-Mar-20230 Days-183/22/20233/22/2023
6504CompleteCompleted22-Mar-20230 Days-193/22/20233/22/2023
6504ApprovalCompleted22-Mar-20230 Days-203/22/20233/22/2023
6504ApprovalCompleted22-Mar-20230 Days-213/22/20233/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-223/22/20233/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-233/22/20233/22/2023
6504Pending System DistributionCompleted22-Mar-20230 Days-243/22/20233/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-253/22/20233/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-263/22/20233/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-273/22/20233/22/2023
6504Tax ApprovalCompleted22-Mar-20230 Days-283/22/20233/22/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-293/21/20233/22/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-303/21/20233/22/2023
6504 Request ReceivedCompleted21-Mar-20230 Days-313/22/20233/22/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-323/21/20233/22/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-333/21/20233/22/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-343/21/20233/22/2023
6504 Request ReceivedInProgress21-Mar-20230 Days-353/21/20233/22/2023
6504ResubmitCompleted22-Mar-20230 Days-363/22/20233/22/2023
6504TaxCompleted22-Mar-20230 Days-373/22/20233/22/2023
6504TaxCompleted22-Mar-20230 Days-383/22/20233/22/2023
6504TaxCompleted22-Mar-20230 Days-393/22/20233/22/2023
3333Account OpenCompleted11-Apr-20230 Days-404/11/20234/11/2023
3333CompleteCompleted11-Apr-20230 Days-414/11/20234/11/2023
3333ApprovalCompleted11-Apr-20230 Days-424/11/20234/11/2023
3333ApprovalCompleted11-Apr-20230 Days-434/11/20234/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-444/11/20234/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-454/11/20234/11/2023
3333Pending System DistributionCompleted11-Apr-20230 Days-464/11/20234/11/2023
3333Tax ApprovalCompleted11-Apr-20230 Days-474/11/20234/11/2023
3333Tax ApprovalCompleted11-Apr-20230 Days-484/11/20234/11/2023
3333 Request ReceivedInProgress10-Apr-20230 Days-494/10/20234/11/2023
3333 Request ReceivedInProgress10-Apr-20230 Days-504/10/20234/11/2023



Labels:
Reply
2 REPLIES 2
binuacs
21 - Polaris
‎06-08-2023 11:22 AM

@Saravanan13 @Use the summarise Tool, Group by CustID field and select Max of End Date then join back the input file with the custID field as key

 

binuacs_0-1686249829565.png

 

Join_080623.yxmd
Reply
Miles_Waller
8 - Asteroid
‎06-08-2023 11:32 AM

To add to @binuacs response, you probably want to make sure your END_DATE column is in a date format, e.g. instead of 5/9/2023 it would appear as 2023-05-09. Also sort the join output by count (assuming that's your RecordID field) to make your output in the same order as your input.

 

Miles_Waller_0-1686249089469.png

 

Reply
Community Live
Labels
Top Solution Authors
View All