How to achieve cumulative sum ordered by data that is equivalent to SQL PARTITION BY
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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".
Solved! Go to Solution.
- Labels:
- In Database
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I learnt something in-turn, thank you for sharing the link!
