Data Have
id
status
1
off
1
on
1
1
2
on
2
off
2
2
3
on
3
off
3
on
3
off
4
off
4
off
4
off
4
off
Data want.
id
status
stat_no
denom
1
off
0
1
1
on
1
1
1
1
0
1
1
0
2
on
1
1
2
off
1
0
2
1
0
2
1
0
3
on
1
1
3
off
1
0
3
on
1
0
3
off
1
0
4
off
0
1
4
off
0
1
4
off
0
1
4
off
0
1
Hello everyone,
I have a table have and i want to create other columns based on the conditions.
stat_no column For Id 1 with status 'off' meaning event has not occurred and the Id is still in the population I want the stat_no as 0, and the next status 'on' indicate an event has occured and the stat_no as 1. So as soon as the status 'on', I want all of stat_no = 1 irrespective of the value of the status within the id group.
Same for id 2, the first status is 'on', I want the rest of the stat_no to be 1 regardless if the value of the subsequent status of that Id.
denom column For Id 1 with status 'off' denom is 1 and if the next value is 'on' then denom = 1 at the point but i want denom to be 0 for the subsequent values with the ID, regardless of the next value of the status.
Thanks.
... View more