Forum Discussion
Find result nearest a target date
Hello,
I have a table that includes the following data about transplants:
- ID of transplant
- Date of transplant
- One or more laboratory results (with the lab date)
Table looks like this:
| GRF # | Dt GRF | Dt Labo #1 | MLL #1 | Dt Labo #2 | MLL #2 | Dt Labo #3 | MLL #3 |
| 1 | 02-févr-22 | 23-févr-22 | 91 | 04-mars-22 | 91 | 11-mai-22 | 93 |
| 2 | 14-févr-22 | 07-mars-22 | 98 | 14-mars-22 | 98 | ||
| 3 | 23-févr-22 | 16-mars-22 | 100 | 23-mars-22 | 100 | 08-juin-22 | 100 |
| 4 | 25-févr-22 | 18-mars-22 | 100 | 25-mars-22 | 100 | ||
| 5 | 01-mars-22 | 22-mars-22 | 100 | 29-mars-22 | 100 | ||
| 6 | 21-mars-22 | 11-avr-22 | 91 | 19-avr-22 | 91 | 28-juin-22 | 100 |
| 7 | 07-avr-22 | 04-mai-22 | 98 | 02-juin-22 | 98 | 05-juil-22 | 99 |
| 8 | 02-mai-22 | 24-mai-22 | 96 | ||||
| 9 | 04-mai-22 | 26-mai-22 | 100 | 01-août-22 | 100 | ||
| 10 | 06-mai-22 | 03-juin-22 | 91 | 06-juil-22 | 91 | 28-juil-22 | 97 |
| 11 | 06-mai-22 | 02-juin-22 | 96 | 11-juil-22 | 96 | ||
| 12 | 14-juin-22 | 29-juil-22 | 59 | 24-août-22 | 59 | ||
| 13 | 15-juin-22 | 14-juil-22 | 92 | 13-sept-22 | 92 | ||
| 14 | 17-juin-22 | 11-juil-22 | 99 | ||||
| 15 | 22-juil-22 | 26-août-22 | 99 | 14-oct-22 | 99 | 24-oct-22 | 100 |
"GRF #" is the ID transplant
"Dt GRF" is date of transplant
"Dt Labo #1" is date of 1st lab
"MLL #1" is the result of the first lab
(same as above for "Dt Labo #2" / "MLL #2" (2nd lab) and "Dt Labo #3" / "MLL #3" (3rd lab)).
What I need is find, for each transplant, the lab result nearest a target date. For each transplant, the target date is 100 days after day of transplant. That lab result should appear in a new column named "Day 100 MLL".
For example, "GRF #1" has May 13th 2022 (100 days after February 2nd 2022) as its target date, so lab #3 (May 11th 2022) will be the nearest lab and "Day 100 MLL" should have 93 as its value.
Thank you for your help!
Hello Mythicos,
Can you please try this approach:
1. Calculate the Target Date
Target Date = [Dt GRF] + 1002. Difference from Target Date
Diff Labo #1 = ABS(DATEDIFF([Target Date], [Dt Labo #1], DAY)) Diff Labo #2 = IF(NOT(ISBLANK([Dt Labo #2])), ABS(DATEDIFF([Target Date], [Dt Labo #2], DAY)), BLANK()) Diff Labo #3 = IF(NOT(ISBLANK([Dt Labo #3])), ABS(DATEDIFF([Target Date], [Dt Labo #3], DAY)), BLANK())3. Nearest Lab Result
Day 100 MLL = SWITCH( TRUE(), [Diff Labo #1] <= [Diff Labo #2] || ISBLANK([Diff Labo #2]) && [Diff Labo #1] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #1], [Diff Labo #2] <= [Diff Labo #1] || ISBLANK([Diff Labo #1]) && [Diff Labo #2] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #2], [MLL #3] )Hope this helps!
2 Replies
Hello Mythicos,
Can you please try this approach:
1. Calculate the Target Date
Target Date = [Dt GRF] + 1002. Difference from Target Date
Diff Labo #1 = ABS(DATEDIFF([Target Date], [Dt Labo #1], DAY)) Diff Labo #2 = IF(NOT(ISBLANK([Dt Labo #2])), ABS(DATEDIFF([Target Date], [Dt Labo #2], DAY)), BLANK()) Diff Labo #3 = IF(NOT(ISBLANK([Dt Labo #3])), ABS(DATEDIFF([Target Date], [Dt Labo #3], DAY)), BLANK())3. Nearest Lab Result
Day 100 MLL = SWITCH( TRUE(), [Diff Labo #1] <= [Diff Labo #2] || ISBLANK([Diff Labo #2]) && [Diff Labo #1] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #1], [Diff Labo #2] <= [Diff Labo #1] || ISBLANK([Diff Labo #1]) && [Diff Labo #2] <= [Diff Labo #3] || ISBLANK([Diff Labo #3]), [MLL #2], [MLL #3] )Hope this helps!
Hello Sahir_Maharaj, I tried your solution and there were some problems with the treatment of blanks.
But I took what you proposed and changed the Switch arguments a little, by first testing the "blank" scenarios and it worked perfectly.
SWITCH(TRUE(), ISBLANK(Chimerism[Diff Labo #2]) && ISBLANK(Chimerism[Diff Labo #3]), Chimerism[MLL #1], ISBLANK(Chimerism[Diff Labo #3]) && ([Diff Labo #1] <= [Diff Labo #2]), Chimerism[MLL #1], ISBLANK(Chimerism[Diff Labo #3]) && ([Diff Labo #2] <= [Diff Labo #1]), Chimerism[MLL #2], ([Diff Labo #1] <= [Diff Labo #2]) && (Chimerism[Diff Labo #1] <= [Diff Labo #3]), Chimerism[MLL #1], ([Diff Labo #2] <= [Diff Labo #1]) && (Chimerism[Diff Labo #2] <= [Diff Labo #3]), Chimerism[MLL #2], Chimerism[MLL #3] )So thank you!
