Table Formula - Cumulative Quantity by Partition

Jozef_783863Jozef_783863 Posts: 263 admin

Can SQL PARTITION BY translate easily into JavaScript for use in Tables: Apply formulas to columns?

Example SQL PARTITION BY:

SUM(Quantity) OVER (PARTITION BY Code Column ORDER BY Index Column) AS CUMULATIVE QUANTITY

Based on the example SQL above and dataset image below, here is what we would like the JavaScript table formula to do:

For each matching Code Column row value, output a running total of the quantity associated to the code. Sort the result by the Index column.

SQL PARTITION BY
https://www.sqltutorial.org/sql-window-functions/sql-partition-by/

Answers

  • Thomas_937381Thomas_937381 Posts: 196

    @Jozef_783863 Perhaps this may be feasible if the formula is in an Excel template file, with Excel: Insert a data table into an Excel template? If that approach does not work, I wonder if it may somehow be possible with CSV: Summarize rows with formulas, but I'm not sure what the JavaScript would be.

  • Jozef_783863Jozef_783863 Posts: 263 admin
    edited July 31

    @Matt_727739, The 7 steps listed below output the following data table (workflow export attached):

    1. Tables: Add a column - Add sort-key column
    2. Tables: Apply formulas to columns - Update sort-key column with custom sort values
    3. Tables: Sort rows - Sort sort-key column
    4. Excel: Create spreadsheet from data table - Save sorted table to XLSX
    5. Excel: Convert to CSV - Convert XLSX to CSV
    6. CSV: Summarize rows with formulas - Javascript to calculate cumulative quantities (see code comments in workflow)
    7. Tables: Apply formulas to columns - Javascript to update original sorted table (see code comments in workflow)
Sign In or Register to comment.