Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
julianortiz
Contributor II
Contributor II

Discard values in QVD

Hey 

I have this data

julianortiz_0-1627581298694.png

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:

julianortiz_1-1627581632544.png

 

Any ideas?

Thanks in advance.

 

 

2 Replies
anthonyj
Creator III
Creator III

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

menta
Partner - Creator II
Partner - Creator II

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