Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

laurischarf
Contributor II

Set analysis with multiple tables

I need to calculate the % of patients with Blood Pressure under 140/90, by year. Seems straightforward, but I'm in need of advice on the best approach to keep only certain patients, based on a comparison of dates from two tables.

My data are in tables with these fields (among others):

  • Conditions: PatientID, ConditionStartDate
  • BP: PatientID, ObservationDate, Systolic, Diastolic
  • Visits: PatientID, VisitDate

I need a bar chart with Year of Visit as the dimension, and % of patients whose last reading of each year was under 140/90. I can include only patients whose ConditionStartDate is earlier than July 1 of the chart Year.

Should I combine the data into a new table so that I can perform the date comparison with set analysis, like this:

EventRowID={"=ConditionStartDate<MakeDate(year(VisitDate), 7, 1)"}

Or should I use the p() function, in my chart measure, with year fields created in the load script, like:

VisitYear=p(BPYear)


Or something else altogether?

Many thanks for your advice.

Tags (1)
1 Solution

Accepted Solutions
krishna_2644
Valued Contributor III

Re: Set analysis with multiple tables

Hope this helps/this could be your staring point.

1.PNG

5 Replies
krishna_2644
Valued Contributor III

Re: Set analysis with multiple tables

some sample data please?

laurischarf
Contributor II

Re: Set analysis with multiple tables

Hi Krishna, thanks for the quick reply.

BP:

PatientIDObservationDateSystolicDiastolic
5454/9/201712276
54510/10/201713980
6783/1/201613479
6782/4/201714191
9105/5/201511969

Visits:

PatientIDVisitDate
5451/9/2016
5454/9/2017
6783/1/2016
6781/10/2017
9105/5/2015

Conditions:

PatientIDConditionStartDate
5452/10/2016
6789/10/2016
91011/11/2010

The bar chart will show Visit Years 2015, 2016, 2017.

Patient 545 will appear in 2016 and 2017 (because his condition started before 7/1 of both years).

Patient 678 will appear in 2017 only (because her condition started after 7/1/2016 but before 7/1/2017).

Patient 910 will appear in 2015 only (because he had no visits in 2016 or 2017).

krishna_2644
Valued Contributor III

Re: Set analysis with multiple tables

Hope this helps/this could be your staring point.

1.PNG

Highlighted
laurischarf
Contributor II

Re: Set analysis with multiple tables

Thank you so much for your help. So generally, do you put all fields into one table, when you need do filtering that compares fields?

krishna_2644
Valued Contributor III

Re: Set analysis with multiple tables

As i was having 2 fields (you can map or do left join) and the data is straight forward i have merged the two tables.but if theres is granularity of data or if theres is case where you shouldnt merge then just associate those two tables with a common field and do the analysis.

Community Browser