Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

pagiamol
New Contributor III

Translate SQL Query to Qlikview Script

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.

Tags (2)