How to achieve cumulative sum ordered by data that is equivalent to SQL PARTITION BY

[shuaib_gc]

I'm using the In-Database nodes to achieve the same effect as the following SQL statement:

SELECT *,

SUM("NET_QTY") OVER (PARTITION BY A."MATNR" ORDER BY "MJAHR","BUDATMKPF") AS "BALANCE"

FROM A

The creates an output where an additional column is added to the output called Balance. The balance is running total of NET_QTY for a particular product/material ordered by a date "BUDATMKPF".

[AbhilashR · Accepted solution]

Hi @shuaib_gc, assuming you are looking to replicate your existing SQL cumulative sum logic within Alteryx, you could use a combination of Sort and Running Total tools to achieve the results. Attached is a sample for your reference. Let us know if this is not what you are looking for.   

[shuaib_gc]

Thanks Abhilash!

Your solution works brilliantly for standard in-memory workflow. Unfortunately, I couldn't find a running total tool for In-Database. However your solution inspired me to find the solution https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Running-Total-Record-ID-using-In-DB-to... by using the SQL expression within the In-Database formula tool.

[AbhilashR]

I learnt something in-turn, thank you for sharing the link!