topic Convert SQL query to QlikView Script in QlikView

Convert SQL query to QlikView Script

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

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.

Re: Convert SQL query to QlikView Script

jonathandienst — Fri, 04 Nov 2016 06:33:17 GMT

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

Re: Convert SQL query to QlikView Script

Marcio_Campestrini — Fri, 04 Nov 2016 10:48:07 GMT

Hi!

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

You can do this in two ways:

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

Hope it helps.

Re: Convert SQL query to QlikView Script

DavidFoster1 — Fri, 04 Nov 2016 11:48:44 GMT

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

Re: Convert SQL query to QlikView Script

Fri, 04 Nov 2016 12:03:01 GMT

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.

Re: Convert SQL query to QlikView Script

Anonymous — Sat, 05 Nov 2016 12:52:24 GMT

Sure. I will.

Re: Convert SQL query to QlikView Script

Anonymous — Sat, 05 Nov 2016 12:54:36 GMT

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.

Re: Convert SQL query to QlikView Script

Anonymous — Sat, 05 Nov 2016 12:55:29 GMT

L means data from Proc_Cyc_ID 75 which is called Latest.

O means data from Proc_Cyc_ID 72 which is called Old.

Re: Convert SQL query to QlikView Script

Anonymous — Sat, 05 Nov 2016 13:12:35 GMT

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;

Re: Convert SQL query to QlikView Script

Marcio_Campestrini — Mon, 29 May 2017 14:10:30 GMT

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