Hello,
I'm leveraging common table expression tables in my SQL code and I want to be able to use the data load editor in Qlik Sense leveraging the same SQL code to load data. Query runs fine outside of Qlik Sense but I'm unable to load when I do that in Qlik . Could you please assist in that regard? Below is what I mean:
with cteInsuredPoltermList as
(
select
pcd.agencycode_ext as agencycode
, pcd.description as agencyname
, acc.accountnumber as accountnumber
, polper.policynumber as policynumber
, polper.id as branchid
, polper.termnumber as policytermnum
, polper.periodstart as poleffdate
, polper.periodend as polexpdate
, min(date(j.closedate)) over
(partition by polper.policynumber) as origpolbounddate
, pol.originaleffectivedate as origpolpereffdate
, date(j.closedate) as bounddate
, row_number() over
(partition by policynumber order by termnumber desc)
as latestseq
from sdatabase1.table2 polper
join sdatabase2.table3 pol
on polper.policyid = pol.id
join sdatabase3.table4 j
on polper.jobid = j.id
join sdatabase4.table5 acc
on pol.accountid = acc.id
join sdatabase5.table6 pcd
on polper.producercodeofrecordid = pcd.id
where pol_line.pctl_hopolicytype_hoe_typecode like 'DP%'
and polper.mostrecentmodel = TRUE
and polper.periodend >= current_date
and UPPER(j.pctl_job_name) <> 'CANCELLATION'
and polper.pctl_policyperiodstatus_name in ('Binding', 'Bound')
)
-- Get Replacement cost and Cov A Limit for the latest branch
-- from PL Reporting layer
, cteInsPolicywithITVList as (
select polterm.agencycode, polterm.agencyname
, polterm.accountnumber, polterm.policynumber
, polterm.poleffdate, polterm.polexpdate
, polterm.bounddate, polterm.origpolbounddate
, polterm.origpolpereffdate
, dwl.replacementcost, cov.covalimit
from cteInsuredPoltermList polterm
left join database1.table1 dwl
on polterm.branchid = dwl.branchid
left join database1.table2 cov
on dwl.branchid = cov.branchid
where polterm.latestseq = 1
)
select hist.agencycode, hist.agencyname
, count(hist.policynumber) as policytally
, count(distinct hist.accountnumber) as accounttally
, max(hist.origpolbounddate) as latestpolicybounddate
, min(hist.origpolbounddate) as earliestpolicybounddate
, max(hist.origpolpereffdate) as latestpolicyeffdate
, min(hist.origpolpereffdate) as earliestpolicyeffdate
, sum(hist.replacementcost) as totalreplacementcost
, sum(hist.covalimit) as totalcovAlimit
from
cteInsPolicywithITVList hist
Best,
Anna
