Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Closest Value

Hi

I have a 2 dates that I need to compare with each other and get the closest date to a certain date.

IE I have a Patient that was serviced on these days.

2013/01/03

2013/01/10

2013/01/23

2013/02/04

2013/03/27

2013/04/11

2013/04/17

Then I have the same patient that got a letter on these days. Lets call this the Dispatched Date.

2013/05/15

2013/06/12

What I need is the following:

PatientID

Service Date

Closest Letter Date

109886-0

2013/01/03

2013/05/15

109886-0

2013/01/10

2013/05/15

109886-0

2013/01/23

2013/05/15

109886-0

2013/02/04

2013/05/15

109886-0

2013/03/27

2013/05/15

109886-0

2013/04/11

2013/05/15

109886-0

2013/04/17

2013/05/15

109886-0

2013/06/02

2013/06/12

Can you help?

I have done the following:

Calculation: Date(min(aggr(if(fabs([Dispatched Date]-ServiceDate) =

min(fabs([Dispatched Date] - ServiceDate)), [Dispatched Date]), [Dispatched Date])))

Dimention: Service Date and PatientID

Now this will give me the closest value to a "Selected" service date. So i have to select the date each time and then according to that service date the calculation will work. I need to do this without a selection.

I got around the problem using a macro to select each service date and export a QVD, then partial reload all the QVD's back into QlikView. This is not good practice. Especially where I want to use this.

Please can anyone help??

Thanks in advance.

Jandre

0 Replies