SQL Query translation in Qlikview scripting language

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

Hello All,

I am running SQL query in my Qlikview script as developed by ETL Team to create a Dashboard in my current project. I am facing very obvious issues of NULL records even when there are no NULL records in the database, so I replaced them using UNKNOWN. the count of records are accurate when I relaod the application but still I am not satisfied with the Script as I am bit confused while working on it.

P. S. - I have never worked on SQL queries in Qlikview script before, that is why.

here is my SQL query from my Project --

OLE DB connection string.

TABLENAME (a single table where all the fields are fetched directly):

Load FIELDS --- PRECEEDING LOAD;

SQL SELECT

--ALL THE ABOVE FIELDS IN SQL MANNER –

A.FIELD1 AS FIELD_1,
B.FIELD5 AS FIELD_5,
C.FIELD18 AS FIELD_7,
FIELD33 AS FIELD_10,
E.FIELD43 AS FIELD_21,

FROM “DATABASE”.DBO.” POL_DTL” A INNER JOIN “DATABASE”.DBO.”POL_LOC_DTL” B ON

A.CYC_ID = B.CYC_ID

AND A.POL_NBR = B.POL_NBR

AND A.REGION = B.REGION

INNER JOIN “DATABASE”.DBO.”CODE_TBL” C ON

B.CYC_ID = C.CYC_ID

AND B.CODE = C.CODE

INNER JOIN “DATABASE”.DBO.”METRIC_SUM” D ON

A.CYC_ID = D.CYC_ID

AND A.REGION = D.REGION

AND A.POL_NBR = D.POL_NBR

AND B.CODE = D.CODE

LEFT JOIN “DATABASE”.DBO.”POL_LAYER” E

ON A.CYC_ID = E.CYC_ID AND LEFT(A.POL_NBR, CHARINDEX(‘ ’,A.POL_NBR)-1) = E.POL_NBR

WHERE A.CYC_ID IN (1,2) AND B.CODE IN (11,22) AND B.REGION IN (CA,NY);

STATUS:

SQL SELECT

ISNULL(L.CYC_ID, O.CYC_ID) CYC_ID,

ISNULL(L.POL_NBR, O.POL_NBR) POL_NBR,

EXPR_DT = CASE WHEN L.CYC_ID IS NOT NULL AND O.CYC_ID IS NOT NULL AND L.EXPR_DT > = O.EXPR_DT THEN L.EXPR_DT

WHEN L.CYC_ID IS NOT NULL AND O.CYC_ID IS NULL THEN L.EXPR_DT

WHEN L.CYC_ID IS NULL AND O.CYC_ID IS NOT NULLTHEN O.EXPR_DT

ELSE NULL END,

ISNULL(L.REGION, O.REGION) REGION,

[STATUS] = CASE WHEN L.CYC_ID IS NOT NULL AND O.CYC_ID IS NOT NULL AND L.EXPR_DT > O.EXPR_DT THEN ‘RENEWED’

WHEN L.CYC_ID IS NOT NULL AND O.CYC_ID IS NOT NULL AND L.EXPR_DT = O.EXPR-DT THEN ‘ACTIVE’

WHEN L.CYC_ID IS NOT NULL AND O.CYC_ID IS NULL THEN ‘NEW’

WHEN L.CYC_ID IS NULL AND O.CYC_ID IS NOT NULL THEN ‘EXPIRED’

ELSE ‘UNKNOWN’ END

FROM

(SELECT CYC_ID , POL_NBR, EXPR_DT, REGION FROM DATABASE.DBO.POL_DTL WHERE CYC_ID IN (1,2) AND CODE IN (11,22) AND REGION IN (CA,NY)) L

FULL OUTER JOIN

(SELECT CYC_ID , POL_NBR, EXPR_DT, REGION FROM DATABASE.DBO.POL_DTL WHERE CYC_ID IN (1,2) AND CODE IN (11,22) AND REGION IN (CA,NY)) O

;

----------no join is specified here-----------------so these are separate table with joins in table viewer---

F TABLE : THIS IS A MASTER CALENDER TABLE

F.CYC ID

F.EFF_DT

----------no join is specified here-----------------so these are separate table with joins in table viewer---

OLE DB CONNECTION STRING FOR NEW DATABASE

G TABLE : THIS IS A VIEW FROM ANOTHER DATABASE

G.CYC_ID

G.REGION

What do I need to do in here??

Table A has almost 50,000 rows

Table B has almost 60,00,000 rows

Table C has 4 rows

Table D has 20,000 rows

Table E has 25,000 rows

I am pulling the data from QVDs. I have optimized them already.

I tried but script execution failed several times during Inner join for the 1st 5 tables. If I relaod A and B with Inner join its coming correct but when I take the whole code, QV fetches 0 records. Sometimes, the execution failed too. I ttried with Debug with Limited Load of 10 rows. So it is showing 0 as well.

Please help. This is something easily critical.

Thank you so much in advance.

topic SQL Query translation in Qlikview scripting language in QlikView

SQL Query translation in Qlikview scripting language