We have patients and status for a program in MonthYears
I am trying to discard values in Script, for example, for patient 876978, if i have more than one record in a MonthYear, i should keep only Active status, but if i only have one record per MonthYear i should keep the same status.
I have used First Sorted Value to take in the first iteration of a Enrollment Status by Patient ID and Enrollment Year Month. Some points to take into account for this to work is that your data would need to be sorted by Patien_ID, StartDate, Enroll_YearMonth. The Enrollment Year Month needs to be a numeric value for the FirstSortedValue to work.
Final: Load Patient_ID, firstsortedvalue(distinct StartDate, Enroll_YearMonth) as StartDate, [Program Name], Enroll_YearMonth, FirstSortedValue(distinct Enrollment_Status, Enroll_YearMonth) as Status Resident temp group by Patient_ID, [Program Name], Enroll_YearMonth;