Hi All,
I need this script in Qlikview.. I extracted the result in QVD and using it currently but I need to create such script which should compare Policies in 2 PROC_CYC_IDs based on user selection in UI Level (Alternate State is unaccepatable by Business Team). For eg. 75 should be comapred with 72, 72 with 69, 69 with 66 likewise.
Status:
LOAD PROC_CYC_ID,
PERIL_CD,
POL_NBR,
POLICY_NBR,
POL_EXPIR_DT,
STATUS;
SQL
SELECT
ISNULL(L.PROC_CYC_ID, O._PROC_CYC_ID) PROC_CYC_ID,
ISNULL(L.POL_NBR, O.POL_NBR) POL_NBR,
LEFT(ISNULL(L.POLICY_NBR, O.POLICY_NBR),15) POLICY_NBR,
POL_EXPIR_DT = CASE WHEN L.PROC_CYC_ID IS NOT NULL AND O.PROC_CYC_ID IS NOT NULL AND L.POL_EXPIR_DT >= O.POL_EXPIR_DT THEN L.POL_EXPIR_DT
WHEN L.PROC_CYC_ID IS NOT NULL AND O.PROC_CYC_ID IS NULL THEN L.POL_EXPIR_DT
WHEN L.PROC_CYC_ID IS NULL AND O.PROC_CYC_ID IS NOT NULL THEN L.POL_EXPIR_DT
ELSE NULL END
ISNULL(L.PERIL_CD, O.PERIL_CD) PERIL_CD,
[STATUS] = CASE WHEN L.PROC_CYC_ID IS NOT NULL AND O.PROC_CYC_ID IS NOT NULL AND L.POL_EXPIR_DT > O.POL_EXPIR_DT THEN 'RENEWED'
WHEN L.PROC_CYC_ID IS NOT NULL AND O.PROC_CYC_ID IS NOT NULL AND L.POL_EXPIR_DT = O.POL_EXPIR_DT THEN 'INFORCE'
WHEN L.PROC_CYC_ID IS NOT NULL AND O.PROC_CYC_ID IS NULL THEN 'NEW'
WHEN L.PROC_CYC_ID IS NULL AND O.PROC_CYC_ID IS NOT NULL THEN 'LOST'
ELSE 'POLICIES IN NEW QUARTER' END
FROM (SELECT PROC_CYC_ID, POL_NBR, POL_EXPIR_DT, PERIL_CD FROM [CAT_OPDB_HIST].[DBO].BI_POL_FINC_DTL WHERE PROC_CYC_ID IN (75) AND CDNT_CD IN (12,25) AND PERIL_CD IN ('EQ','HU')) L
FULL OUTER JOIN
(SELECT PROC_CYC_ID, POL_NBR, POL_EXPIR_DT, PERIL_CD FROM [CAT_OPDB_HIST].[DBO].BI_POL_FINC_DTL WHERE PROC_CYC_ID IN (72+) AND CDNT_CD IN (12,25) AND PERIL_CD IN ('EQ','HU')) O
ON L.POL_NBR = O.POL_NBR AND L.PERIL_CD = O.PERIL_CD;
