Hey all! I have posted and solved one question so far. I am still learning and appreciate the guidance.
My next topic is trying to get specific member and date range combinations. I need to find all members who have an Emergency Room Stay (ER) within 1 day of an inpatient admission (IP) where the ER stay has a copay>$0.
SQL :
Select member_id, service_date, place of service, sum(copay) from (select distinct member_ID, Service_Date from claims where place_of_service='IP' ) a, claims b where a.member_id=b.member_id and b.service_dt between a.service_date-1 and a.service_date and b.place_of_service='ER' having sum(copay)>0
How do I do that in Qlik Sense. I do not have access to the data model so I need to use sets to get me where I want to go. I can't figure out how to get the combination of people and dates. I also only have one date field to work with.
I can get all people that have an inpatient stay and sum their copay:
=sum({< [Service First Date]={'>=01/01/2018 <=06/30/2018'},
[Encrypted Member ID]={"=sum({1<[Service First Date]={'>=01/01/2018 <=06/30/2018'}, [Place of Service]={'21 Inpatient Facility'}>} [Amount Copay])>=0"}>}
[Amount Copay])
Is there a way to use Concat to get Member and Inpatient admit date?