Qlik Sense Mapping in dateranges

[rhinobundy]

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?

[petter]

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))

[petter]

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....