Convert SQL query to QlikView Script

[Anonymous]

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.

[jonathandienst]

I suggest that you upload text rather than an image if you want help with this question.

[Marcio_Campestrini]

Hi!

First of all you need to convert the result of query into a QVD file.

You can do this in two ways:

1. Create a QVD File for each table and then made needed transformations into qlikview.
2. Create a QVD File with the result of your SQL query. Then you simple need to read it on qlikview.

Hope it helps.

[DavidFoster1]

What is the significance of the L and O sub-queries?

[#5]

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.

[Anonymous]

Sure. I will.

[Anonymous]

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.

[Anonymous]

L means data from Proc_Cyc_ID 75 which is called Latest.

O means data from Proc_Cyc_ID 72 which is called Old.

[Anonymous]

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;

[Marcio_Campestrini]

Got you issue solved? Please mark helpful/correct answers.