Complicated Matching

cbaqir — Fri, 16 Aug 2019 21:28:06 GMT

Requirement: Combo Chart Avg number of DEFECT_ID per month per GOV_DECISION_BODY_SUB based on associated Agenda Date.

Grouped by Calendar year
y-axis - avg number of tickets per month
x-axis GOV_DECISION_BODY_SUB
legend Year
Combo chart
vert bars - each year
line for how many DEFECT_ID each year only saw 1 group (only 1 GOV_DECISION_BODY_SUB per DEFECT_ID)

Any DEFECT_ID can have up to 6 GOV_DECISION_BODY_SUB values. For each GOV_DECISION_BODY_SUB, I need to pull the associated Agenda Date.

In some cases, the GOV_DECISION_BODY_SUB will match X_DECISION_BODY (Primary, Secondary, Third etc) and in other cases GOV_DECISION_BODY_SUB will match X_DECISION_COMMITTEE_CAT (Primary, Secondary, Third etc).

For example, DEFECT_ID = 12407 has two GOV_DECISION_BODY_SUB values: ECG and ACG - Case Management.

ECG is the PRIMARY_DECISION_BODY and the PRIMARY_AGENDA_DATE is 4/22/2019.

ACG - Case Management is the SECONDARY_DECISION_COMMITTEE_CAT and the SECONDARY_AGENDA_DATE is 5/18/19.

If this were the only DEFECT_ID, I would need to show an average of 1 ticket for ECG for April 2019 and 1 ticket for ACG - Case Management for May 2019.

Obviously, there will be many DEFECT_IDs for each Agenda Date Month and each GO.V_DECISION_BODY_SUB. How do I accomplish this?

topic Complicated Matching in QlikView

Complicated Matching