Hello all,
I'm trying to replicate the results of a SQL cte in the QlikView load script. All the necessary data is stored in QVDs and are loaded into the QVW each morning. Therefore the SQL process seems unnecessary.
The SQL process basically calculates the ending A/R balance at the end of each accounting period (acctPeriod) by A/R (arid) and Provider ID (doc_no). I'm able to load the current amount for each period in Qlik properly. The balance I can pull the ending as of the last period properly, but all previous are being loaded as the current ending. I'm not sure if I should do this in the script or just attempt to use set analysis in the front end. Any ideas would be greatly appreciated.
/*----SQL Ending AR Query----*/
with cte as (
select distinct e.doc_no, f.arid, f.acctPeriod
from dbo.wcsvc e, dbo.acctDates f) select cte.*,
(
select ISNULL(sum(svc_amt),0) from dbo.wcsvc g
where g.doc_no = cte.doc_no and g.arid_no = cte.arid and g.svc_period = cte.acctPeriod
)
as CurAmount,
(
select ISNULL(sum(svc_amt),0) from dbo.wcsvc g
where g.doc_no = cte.doc_no and g.arid_no = cte.arid and g.svc_period <= cte.acctPeriod
)
as CurBalance,
acctDates.accountingDate,
'ENDAR' as datacode
from cte
left join dbo.acctDates on cte.acctPeriod = acctDates.acctPeriod and cte.arid = acctDates.arid
order by doc_no, arid, acctPeriod ASC
;
/*-----Qlik script-----*/
CurAmount:
LOAD
%Key_arid,
%Key_acctDates,
%Key_doc,
Sum(svc_amt) AS CurAmount,
Sum(IF(svc_period<= svc_period,svc_amt)) as CurBal
Resident [wcsvc]
Group by
%Key_arid,
%Key_acctDates,
%Key_doc;
CurBalance:
LOAD
%Key_arid,
%Key_doc,
Sum(svc_amt) AS Balance
Resident [wcsvc]
Group by
%Key_arid,
%Key_doc;
