Table Formula - Cumulative Quantity by Partition

Options
Jozef_783863
Jozef_783863 Posts: 331 admin
in Questions

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/

Tagged:

Answers

  • Thomas_937381
    Thomas_937381 Posts: 196
    Options

    @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_783863
    Jozef_783863 Posts: 331 admin
    edited July 2020
    Options

    @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)

Categories