Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
Attached is the snapshot from our Qlikview script where we are using SQL query. Now., I need to convert this into QV script. Please help me in doing so.
Your help is highly appreciated.
Thank you.
Regards,
Pagi.
I suggest that you upload text rather than an image if you want help with this question.
Hi!
First of all you need to convert the result of query into a QVD file.
You can do this in two ways:
Hope it helps.
What is the significance of the L and O sub-queries?
First of all, you have to create the connection (use the panel below the script mode). Then, LOAD the attributes and you can do the SQL query. The structure is like this one:
LOAD*;
SQL SELECT *
FROM "YOUR_TABLE";
I hope you find it useful.
Sure. I will.
I did the Extraction in Tier 1 and QVD load in Tier 2 but the code is in SQL which I don't need so need to write Qlikview. Script for the same.
L means data from Proc_Cyc_ID 75 which is called Latest.
O means data from Proc_Cyc_ID 72 which is called Old.
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;
Got you issue solved? Please mark helpful/correct answers.