Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 ;