topic Re: Qlik Sense Mapping in dateranges in App Development

Qlik Sense Mapping in dateranges

rhinobundy — Sat, 31 Mar 2018 17:40:37 GMT

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?

Re: Qlik Sense Mapping in dateranges

petter — Sun, 01 Apr 2018 07:24:47 GMT

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

Re: Qlik Sense Mapping in dateranges

petter — Mon, 02 Apr 2018 11:27:21 GMT

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