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.
