Reset Counter When Field Changes

[dcohen13]

Good afternoon!

Can someone help me with my load script? 

I need to have a counter that increments up when the [Value] is larger than "X", then resets to 0 when the date changes. The [Increment] field is what I want to add in the script.

So essentially, it will go , 0 -> 0-> 0-> and increment up 1 if the [Value] is larger than "X", and resets back to 0 when the [Date] changes.

Date	Value	Increment
3/29/2022	0.00	0
3/29/2022	0.00	0
3/29/2022	0.00	0
3/29/2022	0.00	0
3/29/2022	1.50	1
3/29/2022	0.00	1
3/29/2022	0.00	1
3/29/2022	0.00	1
3/30/2022	0.00	0
3/30/2022	0.00	0
3/30/2022	0.00	0
3/30/2022	0.00	0
3/31/2022	0.00	0
3/31/2022	0.00	0
3/31/2022	0.00	0
3/31/2022	0.00	0
3/31/2022	1.50	1
3/31/2022	0.00	1
3/31/2022	0.00	1
3/31/2022	0.00	1

[rubenmarin]

Hi yoy can use Peek() to acces the last loaded row values, so you ca check if the date is the same than the previous one, and add 1 to the increment if needed.

SET vValueLimit=1; //as an example of "X"
IncrementaTable:
LOAD
  Date,
  Value,
  If(Date=Peek(Date)
    ,If(Value>$(vValueLimit)
      ,RangeSum(Peek(Increment),1) // Add 1 to increment
      ,Peek(Increment) // Keep increment as it was on last row
    ,0 // Reset to 0; Maybe it would be If(Value>$(vValueLimit),1,0)
  ) as Increment
Resident OriginalData 
Order By Date;

DROP table OriginalData ;