When I report count(CASE_NO) against a particular HAZARD, I also need to show other hazard count in the same column.

[Mani_K]

I have below requirement in Qlik. I am using a Pivot where count(CASE_ID) is my measure. And HZRD_TYPE is used as row and column. But along with matching HZRD, i also need to show cases reported in other hazards simultaneously . This is to show when one particular hazard happened, what are the other hazards happening and to understand the relation between hazards.

Attached are excel numbers and what i got in Qlik. Is there a way that I can achieve this in Qlik similar to Excel?

[rubenmarin1]

Hi, it could be done loading data as 2 dimensions, one could be "Hazard" and the other "Related hazard", "Secondary hazard", or something similar, having 2 dimensions you could know wich hazards have relations with other

[Mani_K]

I could achieve this at oracle table level using below SQL. But I am not sure how to convert this into Qlik measure. 

select b.HZRD_TYPE_TX, count(b.CASE_ID) from DWH.DW_CRM_CASE_DV a, DWH.DW_CRM_HAZARD.DV b
where a.CASE_ID = b.CASE_ID
and a.CREATED_DT_TM >= '01/JUL/23' and a.CREATED_DT_TM <= '30/JUN/24'
and b.case_id in (select case_id from DWH.DW_CRM_HAZARD.DV
where HZRD_TYPE_TX = 'Bullying'
)
group by b.HZRD_TYPE_TX order by b.HZRD_TYPE_TX
;

May I request any help here please?

[rubenmarin1 · Accepted solution]

Hi, I don't really know what is that doing, I don't see 2 dimensions for hazards in that query.

Just guessing but maybe you can try loading as:

Hazard:
LOAD CASE_ID as IdCase,
  HZRD_TYPE_TX as Hazard
;
SELECT CASE_ID, HZRD_TYPE_TX From DWH.DW_CRM_HAZARD_DV;

Related:
LOAD CASE_ID as IdCase,
  HZRD_TYPE_TX as RelatedHazard
;
SELECT CASE_ID, HZRD_TYPE_TX From DWH.DW_CRM_HAZARD_DV;

Cases:
LOAD CASE_ID as IdCase,
  CASE_ID
;
SELECT CASE_ID from DWH.DW_CRM_CASE_DV;

And try with a table with Hazard as horizontal dimension, RelatedHazard as vertical dimension and Count(CASE_ID) as expression.

[Mani_K]

Thank you, this solution worked out.