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

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

Mani_K — Thu, 01 May 2025 05:13:33 GMT

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?

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

rubenmarin — Sat, 03 May 2025 06:36:29 GMT

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

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

Mani_K — Tue, 06 May 2025 11:07:02 GMT

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?

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

rubenmarin — Tue, 06 May 2025 12:06:30 GMT

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.

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

Mani_K — Thu, 08 May 2025 02:35:59 GMT

Thank you, this solution worked out.