Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement like this. How do I derive the new column?
Customer Alert NewColumn
A 1 1
A 0 0
A 0 0
A 1 1
A 1 2
A 1 3
Thanks
Try something like this,
tab1:
LOAD *, If(Customer=Peek(Customer) And Peek(Alert)=0 And Alert=1,1,If(Alert=0,0,RangeSum(Peek(NewColumn),1))) As NewColumn;
LOAD RecNo() As RowID,* INLINE [
Customer, Alert
A, 1
A, 0
A, 0
A, 1
A, 1
A, 1
];
This is not a requirement. You posted a sample set of data. Do you want to explain your requirement?
Hi Steve,
Sorry for not being very clear.
Wherever the Alert for an given customer ends at 0 and starts with 1, the new column should be sequenced accordingly as 1,2,3 as I showed in the new column.
Thanks
Try something like this,
tab1:
LOAD *, If(Customer=Peek(Customer) And Peek(Alert)=0 And Alert=1,1,If(Alert=0,0,RangeSum(Peek(NewColumn),1))) As NewColumn;
LOAD RecNo() As RowID,* INLINE [
Customer, Alert
A, 1
A, 0
A, 0
A, 1
A, 1
A, 1
];
Ok, you can use peek for this.
source_data:
load Customer, Alert inline [
Customer, Alert, NewColumn
A, 1, 1,
A, 0, 0,
A, 0, 0,
A, 1, 1,
A, 1, 2,
A, 1, 3,
B, 1, 1,
]
;
data:
Load
Customer
,Alert
,if(peek(Customer) = Customer,
If(Alert=1, peek(NewColumn) + 1, 0)
,if(Alert=1, 1, 0)) as NewColumn
resident source_data
order by Customer asc;
drop table source_data;
exit script;
Thank you so much Saravanan. Your logic really helped a lot.
Thank you so much Steve for your help and the approach you provided. Really appreciate it.