Can you use DPS to extract accounts by sub account last contact date
I was wondering if there is a way (using DPS)to extract a list of accounts by most recent contact date. I need to find account holders who have not made contact with us for more than 5 years, each account they have with us has several sub accounts and each sub acct has a different last contact date, if any one of the sub accounts has a contact date within the 5-year time period all their accounts are exempt from the list. If all their sub accounts have contact dates older than the five years then they should be on the list- as an example…
Account | Sub Account | Last Contact date | Notes | ||||
|---|---|---|---|---|---|---|---|
1111 | 1 | 06/10/2019 | The most recent "sub" account date for 1111 is more than 5 years ago-that acct (1111) would be selected | ||||
1111 | 2 | 07/02/2019 | |||||
1111 | 3 | 06/10/2019 | |||||
1111 | 4 | 12/21/2018 | |||||
1111 | 5 | 06/06/2016 | |||||
1010 | 1 | 03/17/2015 | Most recent sub acct date for 1010 is more than 5 years ago-that acct (1010) would be selected | ||||
1010 | 2 | 11/14/2019 | |||||
3333 | 1 | 03/15/2019 | Most recent date for 3333 is more than 5 years ago-that acct (3333) would be selected | ||||
3333 | 2 | 01/10/2018 | |||||
2020 | 1 | 01/09/2025 | Both of 2020's contact dates are not 5 or more years old this acct would not be selected | ||||
2020 | 2 | 01/09/2025 | |||||
3030 | 3 | 01/09/2025 | While 3030 does have one sub account with a 2010 date the most recent contact date within the sub account group is 2025, so 3030 would not be selected | ||||
3030 | 4 | 01/09/2025 | |||||
3030 | 5 | 04/21/2010 | |||||
3030 | 6 | 12/27/2024 | |||||
3030 | 7 | 07/01/2020 | |||||
6666 | 1 | 11/01/2013 | The account 6666 has 3 sub accounts and one has a date older than 5 years, but the most recent sub acct date is within the 5-year window so 6666 would not be selected | ||||
6666 | 2 | 01/07/2025 | |||||
6666 | 3 | 05/30/2023 | |||||
Answers
-
Hi,
Yes this is definitely possible. Would you just need the list of accounts out as a list? If so, I've attached an example workspace with the sample data csv I used, which should get you started. The only thing I would suggest looking at immediately is improving upon the formula I added, so as to better account for leap years.
Best,
Roland
0
Altair Employee