Translate SQL Query to Qlikview Script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

topic Translate SQL Query to Qlikview Script in QlikView

Translate SQL Query to Qlikview Script