Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a question about mapping data with date ranges
Example:
I have a patient that takes medicines on different days. When that patient goes to the doctor the doctor has to check if a patient had medicines the past 10 days and if yes when was the last time.
I have two tables
Table 1
Patientnumber
Date of visit the doctor
Table 2
Patientnumber
Date of taking medicine
Table 1 | Visit the doctor | |
Patientnumber | date_visit | date_visit_minus 10 |
x | 02-02-2018 | 23-01-2018 |
x | 10-02-2018 | 31-01-2018 |
x | 23-02-2018 | 13-02-2018 |
y | 03-02-2018 | 24-01-2018 |
y | 15-02-2018 | 05-02-2018 |
Table 2 | |
Patientnumber | date medicine |
x | 31-01-2018 |
x | 12-02-2018 |
x | 24-02-2018 |
y | 26-01-2018 |
y | 03-03-2018 |
I want to make table 3 with | ||||
Visitdate with the last date of medicine if exists, max date and counter =1. =0 | ||||
Otherwise date is null and counter |
Patientnumber | date_visit | date_medicin | counter |
x | 02-02-2018 | 31-01-2018 | 1 |
x | 10-02-2018 | 31-1-2018 | 1 |
x | 23-02-2018 | null | 0 |
y | 03-02-2018 | 26-1-2018 | 1 |
y | 15-02-2018 | null | 0 |
How can I manage to get table3 in the editor of Qlik Sense?
This should work for you:
The expression to calculate 1 or 0 is:
-(date_visit-Max(If([date medicine]<=date_visit, [date medicine]))<=10)
To get the number of days from the last visit that the patient took their medicine:
date_visit-Max(If([date medicine]<=date_visit, [date medicine]))
To get the color coding you can use this expression in the Background Color Expression:
If(date_visit-Max(If([date medicine]<=date_visit, [date medicine]))>10,Red(30),Green(40))
If the suggestion answered your question please mark the answer as answered to close the thread. If not please tell us what you need to get more information on....