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