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:
Floor([Snapshot Date]) as [Snapshot Date Master],
Floor(MonthEnd(AddMonths([Snapshot Date], -IterNo() + 1))) as [Snapshot Date]
FROM 'lib://QVDLib/PositionMonthlySnapshot.qvd' (qvd);
INNER JOIN ([Position T12 Tmp])
LOAD [Snapshot Date], [Snapshot Date Master]
RESIDENT [T12 Months];
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?
No it's not the full script, just the part equivalent to the SQL. The T12 Table is deleted after the join. T12Months contains the 12 months for each [Snapshot Date]. The idea is to sum the [Position Management Fees] for each T12 for each month. The expected result is:
Jan 31 2015
Dec 31 2014
So basically, I need a T12 calculation for each entry in position_monthly_fact.