Trying to replicate a SQL CTE load from data stored in QVD.

Jacob_Poole — Mon, 28 Oct 2019 15:34:09 GMT

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;

topic Trying to replicate a SQL CTE load from data stored in QVD. in QlikView

Trying to replicate a SQL CTE load from data stored in QVD.