You can view our full solution workflow below and
download it here!
 |
| Our full solution workflow. |
There are 3 aggregations required for this challenge. We’ll tackle the easier two first.
Aggregate to get the total patient visits and first visit dates
After importing the data simply create a new aggregation step with the following settings:
- GROUP on [Patient ID]
- COUNT DISTINCT [VisitID] fields and rename to [Total Patient Visits].
- You could also SUM the [Number of Rows] field for this.
- Take the MIN of [Date of Service] and rename to [First Visit Date].
 |
| Aggregating to get the total patient visits and first visit dates. |
This gives us the [Total Patient Visits] and [First Visit Date]s for each patient. We can now set this to one side whilst we go back and perform the last aggregation required.
Use a self-join & aggregation to number the visits.
We now need to number each patient’s visits so that their first visit is number 1, second visit is number 2, and so on. To achieve this we can use the running sum trick.
First, duplicate the data in a new clean step. Then join the data to itself on
- [PatientID] = [PatientID]
- [Date of Service] >= [Date of Service].
This means for each patient and each visit, we now have the data for all previous visits joined in. This means the first visit for patient X will have one row. The second visit will have two rows. The third will have three, and so on.
 |
| Self-join settings and example rows. |
By using an aggregation step we can count these rows and therefore number each visit:
- GROUP on every field from the left of the join (every field that doesn’t end in a “-1”).
- This is because we need all the original fields in our output.
- SUM up the [Number of Rows] and rename to [Patient Visit Number].
NOTE: As of version 2019.3.2, when you rename the [Number of Rows] field in an aggregation it
will no longer appear in the aggregation panel however it
will still be aggregated as intended. This can be seen in the screenshot below.
 |
| Aggregation settings for the patient visit numbers. |
 |
| Join settings. |
Join both aggregations together
Next, we need to attach the outputs of both aggregations together. This can simply be done on:
- [PatientID] = [PatientID],
as the
[Total Patient Visits] and
[First Visit Date] needs to be attached to every single visit for each patient.
Flag new patients
Finally, we can create a new calculated field to flag up when we have a new patient in the data. We can use our
[Patient Visit Number] to help with this:
[New Patient Flag]
|
IF [Patient Visit Number] = 1
THEN "New
Patient"
ELSE "Returning
Patient"
END
|
