Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there. I am struggling with something that I think is quite basic but cannot figure out. I have had a look at the previous and peek functions, with no success. What I would like is to create a new field called fmonth that looks at PurchGT0 and is like an ever indicator - i.e. if it becomes 1, it stays 1 for the rest of that ID value and then resets itself at the start of the next ID value. The size of the data could change depending on the number of ID's and the number of months.
TTable:
LOAD * INLINE [
ID, Month, Purch, PurchGT0
1, 201201, 100, 1
1, 201202, 100, 1
1, 201203, 0, 0
1, 201204, 100, 1
1, 201205, 150, 1
1, 201206, 300, 1
2, 201201, 0, 0
2, 201202, 0, 0
2, 201203, 600, 1
2, 201204, 0, 0
2, 201205, 0, 0
2, 201206, 100, 1
];
Desired outcome:
//ID, Month, Purch, PurchGT0, fmonth
//1, 201201, 100, 1,1
//1, 201202, 100, 1,1
//1, 201203, 0, 0,1
//1, 201204, 100, 1,1
//1, 201205, 150, 1,1
//1, 201206, 300, 1,1
//2, 201201, 0, 0,0
//2, 201202, 0, 0,0
//2, 201203, 600, 1,1
//2, 201204, 0, 0,1
//2, 201205, 0, 0,1
//2, 201206, 100, 1,1
I'm stumped so any help would be massively appreciated!
use if(isnull(PurchGT0),peek(PurchGT0),PurchGT0) as fmonth
Thanks Rajni but I end up with fmonths exactly the same as PurchGT0?