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

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

Transformation

In Database

In-Database

Accepted answers

AbhilashR

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.

sample.yxmd

All comments

AbhilashR

sample.yxmd

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-tools/m-p/34640/highlight/true#M13800 by using the SQL expression within the In-Database formula tool.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703