Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am New to Qlik and in need of help.
I am using queries instead of loading tables then filtering because tables are having huge data and many tables are there so it will take more time than we can afford.
Below query is working fine when I am using PK_ID = 1234 (some hardcoded value) instead of highlighted one.
Please help.
This is my query
SELECT info.CONSTRAINTID,
info.CONST_DESC,
info.CONSTRAINT_TYPE,
info.OSLTYPE,
info.PROFILE_TYPE,
info.GATE ,
info.TYPE_H_S,
mdi.WEEK_NUMBER as WEEK,
mdi.PERIOD_DATE PERIODDATEDISP,
info.CODE PK_CODE,
info.DESCRIPTION CON_DESCRIPTION,
mdi.REF_WEEK REFWEEK,
type O_TYPE,
MN_BACK_LOG_COUNT_WK,
NULL MN_BACK_ORD_COUNT_WK,
MN_CURR_CAP_SUM_WK,
MN_TOT_ORD_COUNT_WK,
MN_DELTA
FROM
( SELECT DISTINCT DT.WEEK_NUMBER,
TO_CHAR(DT.PERIOD_DATE,'MM/DD/YYYY') PERIOD_DATE,
DT.PERIOD_DATE REF_WEEK
FROM
(SELECT year_date,
WEEK_NUMBER,
MIN(period_Date) over (partition BY year_number,week_number) period_Date
FROM xyZ.***_****_***
) DT
WHERE DT.year_date>=TRUNC(sysdate)
AND YEAR_DATE BETWEEN
(SELECT to_date(param_value,'YYYY-MM-DD')
FROM random_par
WHERE PARAM_KEY = 'Todays_Date'
)
AND (SELECT add_months(to_date(param_value,'YYYY-MM-DD'),6)
FROM random_par
WHERE PARAM_KEY = 'Todays_Date')
) mdi
LEFT OUTER JOIN
(SELECT MD_DATE.CONSTRAINTID CONSTRAINTID,
MD_DATE.CONST_DESC,
MD_DATE.CONSTRAINT_TYPE,
MD_DATE.OSLTYPE,
MD_DATE.PROFILE_TYPE,
MD_DATE.GATE ,
MD_DATE.TYPE_H_S,
MD_DATE.WEEK_NUMBER,
MD_DATE.PERIOD_DATE PERIOD_DATE_DISP,
MD_DATE.CODE,
MD_DATE.DESCRIPTION,
MD_DATE.REF_WEEK,
'Production' type,
MN_BACK_LOG_COUNT_WK,
NULL MN_BACK_ORD_COUNT_WK,
MN_CURR_CAP_SUM_WK,
MN_TOT_ORD_COUNT_WK,
NVL(MN_TOT_ORD_COUNT_WK,0)-NVL(MN_CURR_CAP_SUM_WK,0) MN_DELTA
FROM
(SELECT *
FROM
( SELECT DISTINCT uc.ccmid,
RDC.CONSTRAINTID,
RDC.CONSTRAINTNAME CONST_DESC,
RDC.UITYPE CONSTRAINT_TYPE,
RDC.OSLTYPE OSLTYPE,
MP1.CODE,
MP1.DESCRIPTION,
CASE
WHEN UC.AUTOUPDATE = 'TRUE'
THEN 'Calendar Dependent'
WHEN UC.BANKABLE = 'TRUE'
THEN 'Bankable'
WHEN UC.AUTOGENERATE = 'TRUE'
THEN 'Autogenerated'
ELSE TO_CHAR(UC.SPK_ID)
END PROFILE_TYPE,
CASE
WHEN UC.CONSTRAINTSTEP=1
THEN
CASE
WHEN UC.LINKCONST IS NOT NULL
THEN 'Production'
||','
||NVL(UC.LINKCONST ,'')
ELSE 'Production'
END
WHEN UC.CONSTRAINTSTEP=2
THEN
CASE
WHEN UC.LINKCONST IS NOT NULL
THEN 'Ex-Factory'
||','
||NVL(UC.LINKCONST ,'')
ELSE 'Ex-Factory'
END
END GATE,
CASE
WHEN UC.N1=0
THEN
CASE
WHEN UP.CCMID IS NULL
THEN 'Hard'
WHEN UP.CCMID IS NOT NULL
AND UP.AMOUNTOVER=1
AND UP.PENALTY =-1
THEN 'Hard'
ELSE 'Soft'
END
WHEN UC.N1=1
THEN 'Soft'
END TYPE_H_S
FROM xyZ.rep_agdud_akaa RDC
JOIN xyZ.UDT_CONSTRAINTMAP UCM
ON RDC.CONSTRAINTID=UCM.CONSTRAINTID
-- AND
-- UCM.MODELID IN
-- ( SELECT DISTINCT MODELID
-- from xyZ.***_**_MAPPING
-- WHERE PK_ID = 9103
-- and PK_LEVEL='WSL'
-- )
JOIN
(SELECT MPM.PK_ID,
MP.CODE,
MP.DESCRIPTION,
MPM.MODELID
FROM
(SELECT *
FROM xyZ.***_**_MAPPING
WHERE PK_LEVEL='WSL'
AND PK_ID IN
( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'
)
) MPM
JOIN ***_** MP
ON MPM.PK_ID = MP.PK_ID
) MP1 ON UCM.MODELID = MP1.MODELID
AND RDC.CONSTRAINTID IN
(SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT
)
AND UCM.MODELID = RDC.MODELID
AND RDC.SCENARIOID = 1
JOIN xyZ.ghd_dhkajanaUC
ON UCM.CCMID=UC.CCMID
LEFT OUTER JOIN xyZ.abhc_aihd UP
ON UP.CCMID =UC.CCMID
WHERE uC.CONSTRAINTSTEP=1
AND UC.CONSTRAINTTAG <>'Objective'
) CONS_INFO,
( SELECT DISTINCT DT.WEEK_NUMBER,
TO_CHAR(DT.PERIOD_DATE,'MM/DD/YYYY') PERIOD_DATE,
DT.PERIOD_DATE REF_WEEK
FROM
(SELECT year_date,
WEEK_NUMBER,
MIN(period_Date) over (partition BY year_number,week_number) period_Date
FROM xyZ.***_****_***
) DT
WHERE DT.year_date>=TRUNC(sysdate)
AND YEAR_DATE BETWEEN
(SELECT TO_DATE(PARAM_VALUE,'YYYY-MM-DD')
FROM random_par
WHERE PARAM_KEY = 'Todays_Date'
)
AND (SELECT ADD_MONTHS(TO_DATE(PARAM_VALUE,'YYYY-MM-DD'),6)
FROM random_par
WHERE PARAM_KEY = 'Todays_Date')
) DT
-- ,
-- (SELECT CODE,
-- DESCRIPTION
-- from xyZ.***_**
-- WHERE PK_ID = 9103
-- ) PK
) MD_DATE
LEFT JOIN
(SELECT RDC.CONSTRAINTID,
COUNT(DISTINCT RFS.ORDERKEY) MN_BACK_LOG_COUNT_WK
FROM xyZ.rep_gfd_xyz RFS,
xyZ.rep_gdkah_djad_fdhlad MISC,
xyZ.rep_agdud_akaa RDC,
xyZ.jghsad_dksad_dsa GFO,
xyZ.***_****_*** DT
WHERE RFS.MODELID IN
( SELECT DISTINCT MODELID
FROM xyZ.***_**_MAPPING
WHERE PK_ID IN
( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'
)
AND PK_LEVEL= 'WSL'
)
AND RFS.SCENARIOID = 1
AND RDC.CONSTRAINTID IN
(SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT
)
AND RFS.PRODUCTIONDATE>=TRUNC(SYSDATE)
AND GFO.FACTORYORDERID =RFS.FACTORYORDERID
AND RDC.ROWKEY =MISC.CONSTRAINTKEY
AND RDC.MODELID =RFS.MODELID
AND RDC.MODELID =MISC.MODELID
AND RDC.SCENARIOID =RFS.SCENARIOID
AND RDC.SCENARIOID =MISC.SCENARIOID
AND MISC.ORDERKEY =RFS.ORDERKEY
AND DT.YEAR_DATE =RFS.PRODUCTIONDATE
AND DT.PERIOD_DATE > NEXT_DAY(TRUNC(SYSDATE-7),'MONDAY')
GROUP BY RDC.CONSTRAINTID
) BACK_LOG_WEEK
ON (MD_DATE.CONSTRAINTID=BACK_LOG_WEEK.CONSTRAINTID )
LEFT OUTER JOIN
(SELECT RDC.CONSTRAINTID,
dt.period_Date RFC_REAL_WEEK,
SUM(
CASE
WHEN upper(rdc.osltype) LIKE '%MIN%'
THEN rfc.target
ELSE rfc.capacity
END) MN_CURR_CAP_SUM_WK
FROM xyZ.REPORT_F_CAPACITY RFC,
xyZ.rep_agdud_akaa RDC,
(SELECT year_date,
MIN(period_Date) over (partition BY year_number,week_number) period_Date
FROM xyZ.***_****_***
) DT
WHERE RFC.MODELID IN
( SELECT DISTINCT MODELID
FROM xyZ.***_**_MAPPING
WHERE PK_ID IN
( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'
)
AND PK_LEVEL= 'WSL'
)
AND RFC.SCENARIOID = 1
AND RDC.CONSTRAINTID IN
(SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT
)
AND RDC.ROWKEY =RFC.CONSTRAINT_KEY
AND RFC.REALDATETIME = DT.YEAR_DATE
AND RFC.REALDATETIME BETWEEN
(SELECT TO_DATE(PARAM_VALUE,'YYYY-MM-DD')
FROM random_par
WHERE PARAM_KEY = 'Todays_Date'
)
AND (SELECT ADD_MONTHS(TO_DATE(PARAM_VALUE,'YYYY-MM-DD'),6)
FROM random_par
WHERE PARAM_KEY = 'Todays_Date')
GROUP BY RDC.CONSTRAINTID,
dt.period_Date
) CAP_WEEK
ON (MD_DATE.CONSTRAINTID=CAP_WEEK.CONSTRAINTID
AND MD_DATE.REF_WEEK =CAP_WEEK.RFC_REAL_WEEK)
LEFT OUTER JOIN
(SELECT RDC.CONSTRAINTID,
dt.period_Date RFS_PROD_WEEK,
COUNT(DISTINCT RFS.ORDERKEY) MN_TOT_ORD_COUNT_WK
FROM xyZ.rep_agdud_akaa RDC,
xyZ.rep_gfd_xyzRFS,
xyZ.jghsad_dksad_dsa GFO,
xyZ.rep_gdkah_djad_fdhlad MISC,
(SELECT year_date,
MIN(period_Date) over (partition BY year_number,week_number) period_Date
FROM xyZ.***_****_***
) DT
WHERE RFS.MODELID IN
( SELECT DISTINCT MODELID
FROM xyZ.***_**_MAPPING
WHERE PK_ID IN
( SELECT DISTINCT pk_id FROM ***_** WHERE active = 'true'
)
AND PK_LEVEL= 'WSL'
)
AND RFS.SCENARIOID = 1
AND RDC.CONSTRAINTID IN
(SELECT DISTINCT(constraintid) FROM REPORT_D_CONSTRAINT
)
AND RFS.PRODUCTIONDATE>=TRUNC(SYSDATE)
AND RFS.FACTORYORDERID =GFO.FACTORYORDERID
AND RDC.MODELID =RFS.MODELID
AND RDC.MODELID =MISC.MODELID
AND RDC.SCENARIOID =RFS.SCENARIOID
AND RDC.SCENARIOID =MISC.SCENARIOID
AND MISC.ORDERKEY =RFS.ORDERKEY
AND MISC.CONSTRAINTKEY =RDC.ROWKEY
AND DT.YEAR_DATE =RFS.PRODUCTIONDATE
GROUP BY RDC.CONSTRAINTID,
dt.period_Date
) TOT_WEEK
ON (MD_DATE.CONSTRAINTID =TOT_WEEK.CONSTRAINTID
AND TOT_WEEK.RFS_PROD_WEEK =MD_DATE.REF_WEEK)
WHERE (MN_BACK_LOG_COUNT_WK IS NOT NULL
OR MN_CURR_CAP_SUM_WK IS NOT NULL
OR MN_TOT_ORD_COUNT_WK IS NOT NULL)
) INFO ON INFO.REF_WEEK =MDI.REF_WEEK;
Thanks in advance for your kind help !!
Note: I forgot one important point ... I am working on Qlik Sense