Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey
I have this data
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.
This is the result that i should have:
Any ideas?
Thanks in advance.
Hi,
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;
I hope this helps.
Thanks
Anthony