Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
Marcio_Campestrini
Specialist
Specialist

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
DavidFoster1
Specialist
Specialist

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

Not applicable
Author

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
Not applicable
Author

Sure. I will.

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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
Specialist
Specialist

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

Márcio Rodrigo Campestrini