Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Convert SQL query to QlikView Script

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.

9 Replies
Highlighted
MVP
MVP

Re: Convert SQL query to QlikView Script

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist
Specialist

Re: Convert SQL query to QlikView Script

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.

Márcio Rodrigo Campestrini
Highlighted
Specialist
Specialist

Re: Convert SQL query to QlikView Script

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

Highlighted
Not applicable

Re: Convert SQL query to QlikView Script

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.

Highlighted
Contributor III
Contributor III

Re: Convert SQL query to QlikView Script

Sure. I will.

Highlighted
Contributor III
Contributor III

Re: Convert SQL query to QlikView Script

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.

Highlighted
Contributor III
Contributor III

Re: Convert SQL query to QlikView Script

L means data from Proc_Cyc_ID 75 which is called Latest.

O means data from Proc_Cyc_ID 72 which is called Old.

Highlighted
Contributor III
Contributor III

Re: Convert SQL query to QlikView Script

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;





Highlighted
Specialist
Specialist

Re: Convert SQL query to QlikView Script

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

Márcio Rodrigo Campestrini