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 · Accepted solution]

Try Below:

If( ID = Previous(ID) and Quarter - Current Quarter=1,BB, (Peek(CC)*AA)+Peek(CC)) AS CC

[swuehl]

Previous(ID) will return NULL for the first line, so you don't get a value back, right?

Try

If( ID <> Previous(ID) and Quarter - [Current Quarter]=1,BB,

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

[alec1982]

Hi,

This is working except for the first row where the value should be 365..

Any thoughts?

Thxs,

Alec

[alec1982]

The ID needs to Equal the previous ID in order to have the right value when we reach ID 2( I mean the first row of ID 2)..

It is not returning null but it is giving the wrong nr.

[alec1982]

Also When i use Previous It gives error that the field doesn't exist while Peek is getting it..

[swuehl]

Right previous(CC) doesn't work, because CC is not a field in your input table.

This works for me:

Set DecimalSep = '.';

Set ThousandSep = ',';

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

ID,         Quarter,    Current Quarter,        AA,              BB ,        CCTemp

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        

];

Though I can't reproduce your CCTemp numbers (how do you calculate 522 from 365* 4.31% +365 ? I do get 380.74

[#8]

It looks like the calculation was 365 * .431 + 365.  It shoudl be 365 * .0431 + 365 which nets the correct 380 I believe.

[#9]

See attached.  This is one possible way to do it via a load script, similar to what others have explained.

[alec1982]

Your calculation is correct..I used 0.431 instead of 0.0431..

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

Multiplying by BB here will get wrong value but if I use Peek(CC) I get the right value..

Any thoughts?

Thxs for all your help!