How to achieve row count until data changes in column?

[michael_vi]

I have a table with 3 columns: _time, type and action

| makeresults count=10
| eval type = "typeA"
| eval action = if((random()%2) == 1, "open", "close")
| union
[| makeresults count=10
| eval type = "typeB"
| eval action = if((random()%2) == 1, "open", "close")]

I need to create a column for each type that would identify the change in the column action and count # of actions in ascending order like this...

_time	typeA	typeB	typeA_count	typeB_count
2022-01-01 05:00:00	open	close	1	1
2022-01-01 05:00:01	open	open	2	1
2022-01-01 05:00:02	close	close	1	1
2022-01-01 05:00:03	open	open	1	1
2022-01-01 05:00:04	close	open	1	2
2022-01-01 05:00:05	open	close	1	1
2022-01-01 05:00:06	open	close	2	2
2022-01-01 05:00:07	open	close	3	3
2022-01-01 05:00:08	close	open	1	1
2022-01-01 05:00:09	open	close	1	1

Thanks

[michael_vi]

And another thing I need help with

I need to get the max action_count from the previous SPL:

2022-12-06 00:03:00	1
2022-12-06 00:04:00	1
2022-12-06 00:05:00	1
2022-12-06 00:06:00	2
2022-12-06 00:07:00	3
2022-12-06 00:08:00	4
2022-12-06 00:09:00	1
2022-12-06 00:10:00	2
2022-12-06 00:11:00	3
2022-12-06 00:12:00	1

TO

2022-12-06 00:03:00	1
2022-12-06 00:04:00	1
2022-12-06 00:08:00	4
2022-12-06 00:11:00	3
2022-12-06 00:12:00	1

[ITWhisperer · Accepted solution]

| makeresults count=10
| eval type = "typeA"
| eval action = if((random()%2) == 1, "open", "close")
| union
[| makeresults count=10
| eval type = "typeB"
| eval action = if((random()%2) == 1, "open", "close")]
| streamstats reset_on_change=true count as action_count by action
| streamstats reset_on_change=true count as type_count by type