Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI
You can use same query in Qlik and store in the qvd and use in the data model
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;
Thanks Iswarya for your response.
I need qliksense script to be implemented.
No Answer yet ???
is this query is too complex?