checking the success of a program by comparing date fields for person IDs

New Contributor



Looking for help on a large data set.  Im trying to assess the success of a program.  Essentially I want to look at all records for each person ID, check if a column A string is present in a different field, and if that string is present, take the MIN date corresponding to a date field in column B.  THEN, grouping by person ID, look at a different column C to see if a string is present, and if so, take a different column D date.  And finally, if column D date is greater than column B date, return something (could just be a tally, or 1).  


I know this sounds like a mouthful (and is) so Im hoping this makes sense. 

3 Replies

Hi @bradyb2323,


Could you please share a file or a screen with a sample of your table? 



Customer IdentifierRx NumberMost Recent Bill DateRx Completed OnPrimary Plan NamePrimary Plan Paid AmountCopay Amount
1234556999966619/13/2022 RETAIL00
123455699955778/26/2022 RETAIL00
1234556875411/9/2022 QSP00
1234556875412/20/2022 RETAIL00
I added test data for the purposes of the question. Hopefully this will make things clearer. Looking to group by Customer Identifier (only one shown in data set), check if "QSP" in Primary Plan Name, and if so, check all RxCompletedOn dates that would fall after the Most Recent Bill Date associated with the QSP line.