In case you missed the announcement: The Alteryx One Fall Release is here! Learn more about the new features and capabilities here
ACT NOW: The Alteryx team will be retiring support for Community account recovery and Community email-change requests after December 31, 2025. Set up your security questions now so you can recover your account anytime, just log out and back in to get started. Learn more 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
binu_acs
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 @binu_acs 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
Fall Release Webinar
Alteryx One Admin Experience
Labels
Top Solution Authors
View All