Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
JacobJones
Creator
Creator

Find the closest date from one field to another field.

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.

example table - initial and reports.png

4 Replies
StarinieriG
Partner
Partner

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
Creator
Creator
Author

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

StarinieriG
Partner
Partner


@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
Creator
Creator
Author

I will try this and let you know, thanks