And may I ask another question related to this problem?
Most probably I will end up moving this calculation to the script. Because I have a mixture of calculations on the Product level and on the Container level, it becomes literally impossible to handle them correctly inside the charts.
So, currently, I ended up doing these calculations in the script and using the following approach :
- create a sorted table of container, date
- loop on each row
- calculate the PRI (as prev_pri * TWRR)
- generate a new row with the current container, date, PRI and add it to a new table
- join this table to another table [container, product, date] for further calculations
This generation of 1 row at a time is too slow and takes around 40 min for 20k records. My real data will be around 300k records, so I can't wait for the whole day for these records to load.
Do you think there might be a faster way for doing this PRI calculation?
Thanks.
***** UPD *****
This is the calculation code inside the script:
// PRI
Performance_Container_tmp2:
NoConcatenate LOAD *
Resident Performance_Container_tmp
order by %CONT, %PERF_DATE;
let pri = 0;
let cont = '';
let prev_cont = '';
for i = 1 to NoOfRows('Performance_Container_tmp2')
// Get prev container
let prev_cont = cont;
// Get current container, date, twrr
let cont = Peek('%CONT', i, 'Performance_Container_tmp2');
let d = Peek('%PERF_DATE', i, 'Performance_Container_tmp2');
let twrr_fact = Peek('TWRR Fact', i, 'Performance_Container_tmp2');
// Set PRI to 0 if the container changed (useful when the container starts in the middle of the month)
if prev_cont <> cont then
let pri = 0;
endif
// Calc PRI
if Day(d) = 1 then
let pri = 1;
ELSE
let pri = pri * twrr_fact;
ENDIF
// Generate new row
if not IsNull(pri) then
Performance_Container_tmpPRI:
LOAD
num($(cont)) as %CONT,
date('$(d)') as %PERF_DATE,
$(pri) as PRI
AutoGenerate 1;
ENDIF
next
// Join PRI calculation to the Container temp table
Left Join (Performance_Container_tmp) LOAD
%CONT,
%PERF_DATE,
PRI as Container.PRI
Resident Performance_Container_tmpPRI;
drop Tables Performance_Container_tmp2, Performance_Container_tmpPRI;
