Hi,
I am trying to translate this sql query into a load script using a qvd file. The idea is to precalculate fees for the trailing 12 months at load to speed up things. This result is used in a complex score calculation that must be precalculated, so I cannot use a calendar to do it on the fly. PositionMonthlySnapshot is a 235 millions rows table.
Here is the SQL that would do the expected behavior:
SELECT client_sk, d.snapshot_date, sum(firmcur_management_fees) FROM bi_poc_demo.position_monthly_fact p
join bi_poc_demo.t12_dates d on p.snapshot_date = d.t12_date
group by client_sk, d.snapshot_date
Here is the load script:
[T12 Months]:
Load
Floor([Snapshot Date]) as [Snapshot Date Master],
Floor(MonthEnd(AddMonths([Snapshot Date], -IterNo() + 1))) as [Snapshot Date]
Resident [Snapshot Dates]
While IterNo() <= 12;
[Position T12 Tmp]:
LOAD [Client ID], [IA Code ID], [Branch ID], [Security ID], Floor([Snapshot Date]), [Position Management Fees],
FROM 'lib://QVDLib/PositionMonthlySnapshot.qvd' (qvd);
INNER JOIN ([Position T12 Tmp])
LOAD [Snapshot Date], [Snapshot Date Master]
RESIDENT [T12 Months];
[Position T12]:
LOAD [Client ID], [Snapshot Date Master], SUM([Position Management Fees])
RESIDENT [Position T12 Tmp]
GROUP BY [Client ID], [Snapshot Date Master];
Although this script would work, the join between [Position T12 Tmp] and [T12 Months] causes a 235M x 12 table, which makes me run out of RAM. Is there a better way to do this using Qlik, or will I have to do it in the warehouse?
Thanks!
