Running Total Calculation Considering Previous Row

[carlcimino]

Hello I am trying to add something like a running total that takes the a calculation on the previous row into consideration for the current row.  Here is my sample data and what I have tried to use. 

RATE_DATA_TEMP:

Load * inline [

RATE_KEY, Check, ROWID, ACTUAL_RATE_CHANGE, STAT_WP
13413, 0.00, 56, 0.075, 5,532.00,
13413, 1.00, 57, 0.052, 5,532.00,
13413, 1.00, 58, 0.024, 5,532.00,
]
;

RATE_DATA:
load *,
if(RATE_KEY=previous(RATE_KEY),

(
(peek('ACTUAL_RATE_CHANGE')*peek('STAT_WP'))+peek('STAT_WP')) +

((peek('ACTUAL_RATE_CHANGE')*peek('STAT_WP'))+peek('STAT_WP'))*[ACTUAL_RATE_CHANGE],

([STAT_WP]*[ACTUAL_RATE_CHANGE])+[STAT_WP]) as STAT_WP_NEW,

resident RATE_DATA_TEMP;

drop table RATE_DATA_TEMP;

I've attached a file showing how it is done in excel and how I would like it to look.  The dollar_change field comes from the result of the prior rows new_stat_wp * the current rows ACTUAL_RATE_CHANGE.  The new_stat_wp is then the sum of that plus the current row dollar_change.

RATE_KEY, Check, ROWID, ACTUAL_RATE_CHANGE, STAT_WP,dollar_change, new_stat_wp
13413, 0.00, 56, 0.075, 5,532.00, 414.90, 5,946.90,
13413, 1.00, 57, 0.052, 5,532.00, 309.24, 6,256.14,
13413, 1.00, 58, 0.024, 5,532.00, 150.15, 6,406.29,

Any help would be much appreciated!

[crusader_ · Accepted solution]

Hi,

Shortly:

first of all you need to handle "first" occurrence of your RATE_KEY and apply corresponding formula (like you did in Excel - it's different to the rest) and create these new fields; = exactly what's happening in "first preceding load"

secondly, you need to apply your new formula to the rows with the same RATE_KEY; = exactly what's happening in the "second preceding load" (with some math simplification applied)

Please check below script:

RATE_DATA:

//Second preceding load below
LOAD RATE_KEY
, Check
, ROWID
, ACTUAL_RATE_CHANGE
, STAT_WP
, IF( RATE_KEY = PEEK(RATE_KEY),
ACTUAL_RATE_CHANGE*PEEK(NEW_STAT_WP),
DOLLAR_CHANGE
) AS DOLLAR_CHANGE
, IF( RATE_KEY = PEEK(RATE_KEY),
PEEK(NEW_STAT_WP)*(1+ACTUAL_RATE_CHANGE),
NEW_STAT_WP
) AS NEW_STAT_WP
;

//First preceding load below

LOAD *,
IF( RATE_KEY <> PEEK(RATE_KEY),
STAT_WP*ACTUAL_RATE_CHANGE) AS DOLLAR_CHANGE,
IF( RATE_KEY <> PEEK(RATE_KEY),
STAT_WP*(1+ACTUAL_RATE_CHANGE)) AS NEW_STAT_WP
;

Load * inline [
RATE_KEY, Check, ROWID, ACTUAL_RATE_CHANGE, STAT_WP
13413, 0.00, 56, 0.075, 5532.00,
13413, 1.00, 57, 0.052, 5532.00,
13413, 1.00, 58, 0.024, 5532.00,
]
;

Please NOTE that when you're using PEEK() function sorting is crucial and if you change a sort order - results will be entirely different.

Hope this helps.

//Andrei

[carlcimino]

That is perfect.  I was trying to jam it all into one load not thinking about using preceding load(s).  Thank you so much!