Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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