2019: Week 21 Solution
You can view our full solution workflow below and download it here!
The USP of this week’s challenge was the fact that it required you to build upon a previous solution and work in new data and new analysis. Whilst some of the original flow could be left as it was, the freshly updated section is highlighted below.
To add in the new patients, we can simply add the new CSV as a data source and drag it onto the canvas. We can then use a Union step to create one long list of all the patients and the dates they first visit the hospital. Finally, we can merge the misnamed [Name] & [Patient Name] together by:
a) Selecting both fields and then clicking merge fields.
b) Drag one field on-top of the other and dropping it to merge the fields.
c) Renaming one of the fields ahead of time to match the other.
For example, if Jose visits the hospital on 20/06/2019 and stays for 6 days, then he leaves on 27/06/2019 and his first check-up is a month later on 27/07/2019.
To begin this process, we first need to join our patient data with the check-up frequencies. Since there is no ‘append’ option yet in Tableau Prep we need to manually create a field in both sets of data to join on. Mine is simply called [Join Field] and contains the letter ‘X’.
Now we need to calculate the date where each patient starts each check-up:
1. Use DATEADD() to add [Length of Stay] in ‘days’ to [Date].
2. Use another DATEADD() to add [Months After Leaving] to the result of the first DATEADD().
This first calculates the date that the patient leaves their first visit, and then calculates when their check-ups start. You can have these in separate fields, however I have nested them both together like below:
Finally, we can union our check-up dates data onto the list of patients' first visits data and continue using the rest of the flow that we built last week! You may need to use of the 3 merge methods described above in order to match up the differently named date fields.
 |
| The full workflow solution. |
The USP of this week’s challenge was the fact that it required you to build upon a previous solution and work in new data and new analysis. Whilst some of the original flow could be left as it was, the freshly updated section is highlighted below.
 |
| The updated section (within the yellow boundary). |
Adding new patients
To add in the new patients, we can simply add the new CSV as a data source and drag it onto the canvas. We can then use a Union step to create one long list of all the patients and the dates they first visit the hospital. Finally, we can merge the misnamed [Name] & [Patient Name] together by:a) Selecting both fields and then clicking merge fields.
b) Drag one field on-top of the other and dropping it to merge the fields.
c) Renaming one of the fields ahead of time to match the other.
Adding check-up dates
Since all patients require check-ups we need to add these check-up dates and lengths onto our list of patient data. It’s important to note here that check-up frequency is measured from the date that a patient leaves their first initial period, not from the first day they visit the hospital.For example, if Jose visits the hospital on 20/06/2019 and stays for 6 days, then he leaves on 27/06/2019 and his first check-up is a month later on 27/07/2019.
To begin this process, we first need to join our patient data with the check-up frequencies. Since there is no ‘append’ option yet in Tableau Prep we need to manually create a field in both sets of data to join on. Mine is simply called [Join Field] and contains the letter ‘X’.
Now we need to calculate the date where each patient starts each check-up:
1. Use DATEADD() to add [Length of Stay] in ‘days’ to [Date].
2. Use another DATEADD() to add [Months After Leaving] to the result of the first DATEADD().
This first calculates the date that the patient leaves their first visit, and then calculates when their check-ups start. You can have these in separate fields, however I have nested them both together like below:
|
[Date]
|
|
DATE(
DATEADD(
'month'
,[Months After Leaving]
,DATEADD(
'day'
,[Length of Stay]
,[Date]
)
)
)
|
Finally, we can union our check-up dates data onto the list of patients' first visits data and continue using the rest of the flow that we built last week! You may need to use of the 3 merge methods described above in order to match up the differently named date fields.
