Help with Load Script for Min Date and Field

[proctors]

I have the following data for one (fake) patient in a table box.

I need the last column to give me the Department that matches the Office Visit Min Date. The Office Visit Min date is calculated as the min Office Visit after the Insurance Date Min. I've tried lots of things and just can't get it to work in my Load Script.

LOAD MRN,

date(min(if([Office Visit Date]>[Primary_Insurance_Date_Min],[Office Visit Date]))) as [Office Visit Date Min]

Resident Data

Group by MRN;

MRN	Primary_Insurance_Date_Min	Office Visit Date Min	Office Visit Dates	Office Visit Department	Office Visit Date Min Department
999	7/21/2016	8/29/2016	2/4/2016	MKT 3440 BEHAVIORAL HL
999	7/21/2016	8/29/2016	8/29/2016	MKT 3440 BH AUTISM
999	7/21/2016	8/29/2016	11/16/2016	MKT 3440 BEHAVIORAL HL
999	7/21/2016	8/29/2016	1/11/2017	MKT 3440 BEHAVIORAL HL
999	7/21/2016	8/29/2016	3/8/2017	MKT 3440 BEHAVIORAL HL
999	7/21/2016	8/29/2016	5/3/2017	MKT 3440 BEHAVIORAL HL
999	7/21/2016	8/29/2016	7/13/2017	MKT 3440 BEHAVIORAL HL

I hope the result could be the following once I remove the Office Visits Column.

MRN	Primary_Insurance_Date_Min	Office Visit Date Min	Office Visit Date Min Department
999	7/21/2016	8/29/2016	MKT 3440 BH AUTISM

[antoniotiman]

Hi Stephon,

try this Script

Table:
LOAD MRN,
Primary_Insurance_Date_Min,
[Office Visit Dates],
[Office Visit Department]
FROM
"https://community.qlik.com/message/1311047"
(html, codepage is 1252, embedded labels, table is @1);
Inner Join
LOAD MRN,Date(Min([Office Visit Dates])) as [Office Visit Dates]
Resident Table
Where [Office Visit Dates] >= Primary_Insurance_Date_Min
Group By MRN;

Regards,

Antonio