Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
julianortiz
Contributor
Contributor

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
Creator

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
Partner

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