Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
dcohen13
Contributor III
Contributor III

Reset Counter When Field Changes

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
Labels (1)
1 Reply
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 ;