Hello All,
Below is the SQL script which I am using in Qlikview script editor. I have extracted the data using SQL query in Tier 1 and loaded the QVD in tier 2 to make application but now I want the script to be in Qlikview code only.
Please help me build it. Also, I have additional requirement -- If I load 4 PROC_CYC_ID :: 66,69,72,75 then on front end when I select 75 the below code should comapre the data (means Policies) with 72 PROC_CYC_ID. Similarly, 72 to be comapred with 69, 69 to be comapred with 66 likewise.
Status_Tbl:
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;
Thanks you so much in Advance.
