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
