Measure Calculation

[Mahamed_Qlik]

Hi All,

Please help me to write the script for below query. I need "COMPANY_SIZE_CATEGORY" as my dimension .

My query is :

select count (P_ID), PC_ID ,
CASE
WHEN COUNT ( P_ID) <= 10
THEN
'Junior'

WHEN COUNT ( P_ID) >= 11
THEN
'Big'
END
COMPANY_SIZE_CATEGORY
FROM (SELECT A.P_ID,
B.CIVIL_ID,
A.PC_ID,
A.INSURANCE_TYPE_ID,
A.CONTRACT_DLVR,
A. RESGN_DLVR,
TO_NUMBER (TO_CHAR (A.CONTRACT_DLVR, 'yyyymmdd')) abc
FROM INS_STS A , INS_MST B
WHERE A.INSURANCE_TYPE_ID <> 5
AND A.P_ID = B.INSURED_ID
AND A.CONTRACT_STATUS = 1
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID
FROM INS_SAL_CHG
WHERE STATUS = 1)
AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <= '202204'
AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE) ,'YYYYMM') >'202204'
)) ) ins_sts_tbl
WHERE abc =
(SELECT MAX (abc)
FROM (SELECT TO_NUMBER (TO_CHAR (CONTRACT_DLVR, 'yyyymmdd'))
abc
FROM INS_STS A
WHERE A.INSURANCE_TYPE_ID <> 5
AND A.CONTRACT_STATUS = 1
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID
FROM INS_SAL_CHG
WHERE STATUS = 1)
AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <=202204
AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE),'YYYYMM') >202204))
AND P_ID = ins_sts_tbl.P_ID))
GROUP BY PC_ID;

Regards,

Mahamed

[MayilVahanan]

HI

You can use same query in Qlik and store in the qvd and use in the data model

[Iswarya_]

Hi @Mahamed_Qlik ,

Please find the script below:

LIB CONNECT TO 'MyDBConnection';

Data:

Load *;

select count (P_ID), PC_ID ,
CASE
WHEN COUNT ( P_ID) <= 10
THEN
'Junior'

WHEN COUNT ( P_ID) >= 11
THEN
'Big'
END
COMPANY_SIZE_CATEGORY
FROM (SELECT A.P_ID,
B.CIVIL_ID,
A.PC_ID,
A.INSURANCE_TYPE_ID,
A.CONTRACT_DLVR,
A. RESGN_DLVR,
TO_NUMBER (TO_CHAR (A.CONTRACT_DLVR, 'yyyymmdd')) abc
FROM INS_STS A , INS_MST B
WHERE A.INSURANCE_TYPE_ID <> 5
AND A.P_ID = B.INSURED_ID
AND A.CONTRACT_STATUS = 1
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID
FROM INS_SAL_CHG
WHERE STATUS = 1)
AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <= '202204'
AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE) ,'YYYYMM') >'202204'
)) ) ins_sts_tbl
WHERE abc =
(SELECT MAX (abc)
FROM (SELECT TO_NUMBER (TO_CHAR (CONTRACT_DLVR, 'yyyymmdd'))
abc
FROM INS_STS A
WHERE A.INSURANCE_TYPE_ID <> 5
AND A.CONTRACT_STATUS = 1
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID
FROM INS_SAL_CHG
WHERE STATUS = 1)
AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <=202204
AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE),'YYYYMM') >202204))
AND P_ID = ins_sts_tbl.P_ID))
GROUP BY PC_ID;

[Mahamed_Qlik]

Thanks Iswarya for your response.

I need qliksense script to be implemented.

[Mahamed_Qlik]

No Answer yet ???
is this query is too complex?