Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

QSense
Not applicable

SQL Query

Hello ,

I want to execute below query in Qlickview.

Is there anybody help me ?

My regards,

create table ZZTCP0VBAVKMD000 nologging as
select a13.ID_GNR_YM ID_GNR_YM,
a12.ID_GNR_CITY ID_GNR_CITY,
sum(case when a11.ID_TKT_RTG_DOMINT_TYPE=1 then (a11.MT_SM_EXCESS_BGG_GROSS_EUR+a11.MT_SM_GROSS_EUR+a11.MT_SM_SPAM_EUR+a11.MT_SM_TOT_DU_EUR+a11.MT_SM_TOT_YR_EUR)/1.18 else (a11.MT_SM_EXCESS_BGG_GROSS_EUR+a11.MT_SM_GROSS_EUR+a11.MT_SM_SPAM_EUR+a11.MT_SM_TOT_DU_EUR+a11.MT_SM_TOT_YR_EUR) end) WJXBFS1
from SM_SALES2 a11
cross join LU_GNR_CITY a12
cross join LU_DT_GNR_YM a13
join LU_AGENT a14
on (a11.ID_AGENT = a14.ID_AGENT)
where (a14.ID_AGENT_MRKTG_CITY = a12.ID_GNR_CITY
and a12.ID_GNR_AREA_GROUP not in (1)
and a13.ID_GNR_YM between 200801 and 201010
and a11.ID_TKT_REPORTING_YM = a13.ID_GNR_YM)
group by a13.ID_GNR_YM,
a12.ID_GNR_CITY

Pass1 - Duration: 0:00:00.04
create index ZZTCP0VBAVKMD000_i on ZZTCP0VBAVKMD000 (ID_GNR_YM, ID_GNR_CITY)

Pass2 - Duration: 0:00:00.14
ANALYZE TABLE ZZTCP0VBAVKMD000 ESTIMATE STATISTICS SAMPLE 10 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 50

Pass3 - Duration: 0:00:00.23
create table ZZTT7IPC6U8MD001 nologging as
select a13.ID_GNR_YM ID_GNR_YM,
a12.ID_GNR_CITY ID_GNR_CITY,
(CASE WHEN sum(a11.MT_MUH_BUT_POS_EUR)=0 THEN NULL ELSE sum(a11.MT_MUH_BUT_POS_EUR) END) WJXBFS1
from BA_SALES_TYPE_YW a11
cross join LU_GNR_CITY a12
cross join LU_DT_GNR_YM a13
where (a11.ID_AGENT_MRKTG_CITY = a12.ID_GNR_CITY
and a12.ID_GNR_AREA_GROUP not in (1)
and a13.ID_GNR_YM between 200801 and 201010
and ((a13.ID_GNR_YM)
in (select c21.ID_GNR_YM
from LU_DT_GNR_YM c21
cross join LU_DT_TKT_REPORTING_YM c22
where c22.ID_TKT_REPORTING_YM = c21.ID_GNR_YM))
and a11.ID_TKT_DATE_OF_TRNC_YM = a13.ID_GNR_YM)
group by a13.ID_GNR_YM,
a12.ID_GNR_CITY

Pass4 - Duration: 0:00:00.04
create index ZZTT7IPC6U8MD001_i on ZZTT7IPC6U8MD001 (ID_GNR_YM, ID_GNR_CITY)

Pass5 - Duration: 0:00:00.11
ANALYZE TABLE ZZTT7IPC6U8MD001 ESTIMATE STATISTICS SAMPLE 10 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 50

Pass6 - Duration: 0:00:00.23
Rows selected: 5246
select distinct a13.ID_GNR_COMP_REG ID_GNR_COMP_REG,
a15.DS_GNR_COMP_REG DS_GNR_COMP_REG,
coalesce(pa11.ID_GNR_CITY, pa12.ID_GNR_CITY) ID_GNR_CITY,
a13.DS_GNR_CITY DS_DET_GNR_CITY,
a13.DS_DET_GNR_CITY DS_DET_GNR_CITY0,
a13.ID_GNR_CNTRY ID_GNR_CNTRY,
a16.DS_GNR_CNTRY DS_GNR_CNTRY,
a16.DS_DET_GNR_CNTRY DS_DET_GNR_CNTRY,
coalesce(pa11.ID_GNR_YM, pa12.ID_GNR_YM) ID_GNR_YM,
a14.DS_GNR_YM DS_GNR_YM,
case when a14.ID_GNR_MONTH in (3,6,9,12) then to_char(to_date(a14.ID_GNR_YM,'YYYYMM') ,'Mon YY') else '.' end CustCol_153,
pa11.WJXBFS1 WJXBFS1,
pa12.WJXBFS1 WJXBFS2
from ZZTCP0VBAVKMD000 pa11
full outer join ZZTT7IPC6U8MD001 pa12
on (pa11.ID_GNR_CITY = pa12.ID_GNR_CITY and
pa11.ID_GNR_YM = pa12.ID_GNR_YM)
join LU_GNR_CITY a13
on (coalesce(pa11.ID_GNR_CITY, pa12.ID_GNR_CITY) = a13.ID_GNR_CITY)
join LU_DT_GNR_YM a14
on (coalesce(pa11.ID_GNR_YM, pa12.ID_GNR_YM) = a14.ID_GNR_YM)
join LU_GNR_COMP_REG a15
on (a13.ID_GNR_COMP_REG = a15.ID_GNR_COMP_REG)
join LU_GNR_CNTRY a16
on (a13.ID_GNR_CNTRY = a16.ID_GNR_CNTRY)

Pass7 - Duration: 0:00:00.03
[Populate Report Data]

Pass8 - Duration: 0:00:00.07
drop table ZZTCP0VBAVKMD000 purge

Pass9 - Duration: 0:00:00.06
drop table ZZTT7IPC6U8MD001 purge