Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Combine People and Dates SQL Example

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.



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?




Labels (1)
0 Replies