I have a very big table consisting of approximately 3 million rows. The table consists of four columns [ID period amount pbehind], see the following screenshot including the output column I want.
For every ID, I want to count the consecutive amount of times of behind payment (pbehind). But there are a few conditions to this:
- when pbehind <= 0.5, then output = 0
- when pbehind > 1.5, but value in previous period is 0, then output = 0;
- when pbehind > 0.5, but amount in next period (which is last period) is zero, then output=0.
There are a couple of things to note:
- period does not have to start at 1 and increase by 1 (see ID = 2). However it is ordered by ID and period using sortrows(table,[1,2]).
- An ID can make multiple payments for a certain period, however pbehind will always be equal to for that period (see ID=3).
I know for the first condition that I can just do the following:
output = pbehind;
But I cannot figure out how to efficiently implement the other conditions. Does anyone know how to do this?