Set analysis: find related rows in a table

[Lauri]

I have a table that lists where people went for care, on what date. The fields are PersonID, PlaceID, Date.

I am trying to find persons who went to a specific PlaceID (101) and also went to any other PlaceID 30 days later.

I am guessing that I should build a new table in the load that does this, or is set analysis an option? I just can't think of how.

[paulselousyoriz · Accepted solution]

Hi Lauri,

I am not sure you could do this completely with set analysis. It tends to be a rule of thumb that it is better for the app performance if you achieve this in the ETL script rather than in Set Analysis. This is an example of how you could achieve what you want in an ETL script:

"

Test:
LOAD * INLINE [
PersonID, PlaceID, Date
1,100,12-6-2020
2,101,19-6-2020
2,102,25-6-2020
3,101,1-1-2020
3,109,1-6-2020
4,110,1-5-2020
5,101,1-6-2020,
4,101,2-5-2020
];

Test1:
Load
PersonID,
PlaceID,
Date,
If(PlaceID=101 and Previous(PersonID)= PersonID and Date#(Previous(Date),'DD-MM-YYYY')-Date#(Date,'DD-MM-YYYY')>30,1,0) As Flag
Resident Test
Order by PersonID,Date desc;

drop table Test;

"

Then the set analysis would just have to include "Flag={1}".

Hope that helps.

Regards,

Paul

[Lauri]

Thanks for validating! Very helpful.