Use of Previous Function

[alec1982]

Hi Guys,

I have a table that looks as follow

ID         Quarter    Current Quarter        AA              BB         CC

1          20133             20132             4.31%         365          365     // Value of BB

1          20134             20132             4.31%         365          522     //365 * 4.31% +365

1          20141             20132             4.31%         365          746     //522 * 4.31+522

1          20142             20132             4.31%         365        1068     //746*4.31% + 746

2          20133             20132             2.22%         245          245    //245

2          20134             20132             2.22%         245          350

2          20141             20132             2.22%         245          501

2          20142             20132             2.22%         245          716        

I am trying to add a new column 'CC' where:

If( ID = Previous ID and Quarter - Current Quarter=1,BB,

(Previous (CC )*AA)+CC))

The column that I am trying to build is Column CC which is colored red..

Thxs for all your help.

[#2]

Hi Brad, Please find the attached file

LOAD *, ROUND(If(  Quarter - [Current Quarter]=1 AND ID <> Peek(ID),  BB,  Peek(CC)*AA +Peek(CC) ))  as CC INLINE [

ID,        Quarter,    Current Quarter,        AA,              BB

1,          20133,            20132,            4.31%,        365

1,          20134,            20132,            4.31%,        365

1,          20141,            20132,            4.31%,        365

1,          20142,            20132,            4.31%,        365

2,          20133,            20132,            2.22%,        245

2,          20134,            20132,            2.22%,        245

2,          20141,            20132,            2.22%,        245

2,          20142,            20132,            2.22%,        245

];

[swuehl]

Thoughts about what? I don't really know what you want to calculate, but basically you need to understand the difference between peek() and previous() functions:

"The main difference is that Previous() fetches data from previous input record, whereas Peek() fetches data from previous output record. (Input to the Load statement and output of the Load statement.)

In addition, Peek() can be used to fetch data from other records than the previous and from other tables than the current, if the second and third parameters are used."

If you've understood that and then look at your input data as it enters your load and as it is output from your load, I believe you can apply the correct logic easily.