Find the closest date from one field to another field.

[JacobJones]

I'm going to try and ask this again more clearly.

Here is my situation: I have a table of rehab treatments. The treatments happen in blocks that start with a service called Initial, then there are treatments. All treatments have a type, Report or No Report. In a block, one treatment will have a type Report. However, there are multiple blocks of treatments, so multiple Initials and treatments of type Report.

Here is what I need to do: I need to find the date difference between the Initial and the treatment with the type Report. But to do that, I need to link the treatment with the type Report to the appropriate Initial. In the end I would want the average date difference between Initials and treatments of type Report.

[StarinieriG]

Hi,

probably you could do something like this:

TABLE:
LOAD *,
If(Type='Initial',RowNo(),Peek('ID')) AS ID
;
...

Order by

Date;

NoConcatenate
TABLE_1:
LOAD
ID,
Max(Date_tr)-Max(Date_initial) AS Difference
Group By
ID
;
LOAD
Service,
Type,
ID,
Date#(If(Type='Initial',Date),'YYYY-MM-DD') as Date_initial,
Date#(If(Type='Treatment (Report)',Date),'YYYY-MM-DD') as Date_tr
Resident TABLE
Where Match(Type,'Initial','Treatment (Report)')>0
Order By
Date
;

[JacobJones]

Would you mind annotating that a bit or explaining the logic of what is going on?

[StarinieriG]

---

@StarinieriG wrote:

Hi,

probably you could do something like this:

Create a unique ID for each group (before, you have to order them by date)

TABLE:
LOAD *,
If(Type='Initial',RowNo(),Peek('ID')) AS ID
;
...

Order by

Date;

Then, create a new table in which you keep only initial and treatment (report). Then, separate each date, so when you group by ID, you're not going to lose both. So, you will have a new table, where for each ID, you have the difference between two dates

NoConcatenate
TABLE_1:
LOAD
ID,
Max(Date_tr)-Max(Date_initial) AS Difference
Group By
ID
;
LOAD
Service,
Type,
ID,
Date#(If(Type='Initial',Date),'YYYY-MM-DD') as Date_initial,
Date#(If(Type='Treatment (Report)',Date),'YYYY-MM-DD') as Date_tr
Resident TABLE
Where Match(Type,'Initial','Treatment (Report)')>0
Order By
Date
;

---

[JacobJones]

I will try this and let you know, thanks