Numbering Requirement

[jpjust]

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

[stevejoyce]

This is not a requirement.  You posted a sample set of data.  Do you want to explain your requirement?

[jpjust]

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

[Saravanan_Desingh · Accepted solution]

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
];

[stevejoyce]

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;

[jpjust]

Thank you so much Saravanan. Your logic really helped a lot.

[jpjust]

Thank you so much Steve for your help and the approach you provided. Really appreciate it.