topic Re: Discard values in QVD in App Development

Discard values in QVD

julianortiz — Thu, 29 Jul 2021 18:01:39 GMT

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.

Re: Discard values in QVD

anthonyj — Fri, 30 Jul 2021 06:07:08 GMT

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

Re: Discard values in QVD

menta — Fri, 30 Jul 2021 08:21:19 GMT

A work around can be group by Patient_ID and year-month and after take the first value from the enrollment_Status. you can group by and order by patient_id and status