Kusto row_cumsum modifying the Term if Term reaches a point

Question

I have a list of Employee names and Salaries in the following order

I need to create the output table in the below format. ie, whenever the accumulated salary-total crosses 3000 I have to detect that and mark that row.

I have tried to do row_cumsum and reset the Term once it crossed 3000 but it didn't work for the second iteration.

datatable (name:string, month:int, salary:long)
[
    "Alice", 1, 1000,
    "Alice", 2, 2000,
    "Alice", 3, 1400,
    "Alice", 3, 1400,
    "Alice", 3, 1400,
]
| order by name asc, month asc
| extend total=row_cumsum(salary) 
| extend total=iff(total >=3000,total-prev(total),total)

Hi Justin, there's going to be an elegant way to achieve this later this month. We'll post the solution here as soon as it's available. — Slavik N, Apr 07 '21 at 13:00

score 2 · Accepted Answer · answered Apr 22 '21 at 08:16

This is now possible with scan operator:

datatable (name:string, salary:long)
[
    "Alice", 1000,
    "Alice", 2000,
    "Alice", 1400,
    "Alice", 1400,
    "Alice", 1400,
    "Alice", 1000,
    "Bob", 2400,
    "Bob", 1000,
    "Bob", 1000
]
| sort by name asc
| scan declare (total:long) with 
(
    step s: true => total = iff(isnull(s.total) or name != s.name, salary, iff(s.total < 3000, s.total + salary, salary));
)
| extend boundary_detected = iff(total >= 3000, 1, long(null))

name	salary	total	boundary_detected
Alice	1000	1000
Alice	2000	3000	1
Alice	1400	1400
Alice	1400	2800
Alice	1400	4200	1
Alice	1000	1000
Bob	2400	2400
Bob	1000	3400	1
Bob	1000	1000

This feature is available only on EngineV3 clusters as per documentation. Accepting the answer. Thanks — Justin Mathew, Apr 22 '21 at 08:54

Kusto row_cumsum modifying the Term if Term reaches a point

1 Answers1

Linked