topic Re: Filter select first instance of in App Development

Filter select first instance of

tdegen_qlik — Sat, 17 Dec 2022 16:51:37 GMT

The concept is easy, but I'm struggling - I think I'm overlooking something basic and I need a little help.

I have a small/medium data set (12k rows) - it's medically related (but no patient identifying info).

To keep the concept simple, let's suppose my data is patient doctor visits (with patient ID, date of visit, gender, age, type of visit, etc).

Suppose I want to see ALL patient visits between 2019 and 2021 for female patients between the ages of 40 and 55 (easy enough).

NOW suppose I want that same data set above, but I only want to see the FIRST time each patient visited. <-- How the heck do I do that?

From a SQL perspective this is pretty straight forward - (1) filter the data set to get the sub set, (2) Grab Patient ID and Date of visit, (3) group by patient ID and determine Max(date of visit), (4) go back to the subset and filter by new criteria of patient/max(date of visit).

But I can't seem to crack this in qlik. I'm missing something. Any help would be appreciated

edit for clarity:

[This is what I need]
Return a data grid of all patients' first visits (by date) between Aug. 2017 and July 2020 where they received a flu shot, and were female, and were between the ages of 45 and 57 at the time of the appointment. Where the user can, in real time switch between male/female, the date range, the age range, everything....

(if there is a clever way of creating helper columns at design time, I'm all in. But as I think about this, I believe this "first visit" must be determined at runtime because "first" changes with every filter reduction)

Re: Filter select first instance of

deepanshuSh — Sat, 17 Dec 2022 12:42:46 GMT

I think you have a pretty good grasp of things already, so maybe this could work and would be easy to understand as well, as you already understand basics of SQL.

Lets assume you have a date fields which consists of date/time of visit of the patient.

Load

patientID, gender, age, type of visit

From Table;

Load

patientID,

min(date) as visit_date //if you want the first time the patient visits and max(date) if you want latest date.

Where Year>=2019 and year<=2021

Age >=40 and age<=50;

Re: Filter select first instance of

tdegen_qlik — Sat, 17 Dec 2022 16:49:37 GMT

I'm afraid that would only ever get the first visit ever by the patient.

I want the first visit date after all the other filters/reduction are applied.

Imagine you were a patient who visited your doctor two or three times each year (every year) since 2010.

Can you answer: What is your first visit between the years 2011 and 2012? What is your first visit between September of 2017 and May of 2020? What is your first visit where you received a flu shot?

If I only had to do this with ONE patient I can do that with sorting and only taking the first row. BUT I need to do this with a dataset of dozens/hundreds of patients.

[This is what I need]
Return a data grid of all patients' first visits (by date) between Aug. 2017 and July 2020 where they received a flu shot, and were female, and were between the ages of 45 and 57 at the time of the appointment. Where the user can, in real time switch between male/female, the date range, the age range, everything....

Re: Filter select first instance of

deepanshuSh — Mon, 19 Dec 2022 16:30:30 GMT

Why not do it on the front end, using the set analysis and aggregation function (aggr) grouping on month/year as well as patient ID? It would be easier for you to test, but a little computation intensive.

Re: Filter select first instance of

tdegen_qlik — Mon, 19 Dec 2022 16:54:39 GMT

That's kinda what I'm asking for help with.

"How do I do this on the front end?"

You are suggesting set analysis and aggr function? I've used set analysis extensively for filtering. But not for getting the "first instance of"

How might that look?