Summarize CSV: Sum Values by Unique Identifier

If I have a .csv file that looks like this:

ID,Number
0001,47
0001,88
0001,33
0002,876
0002,23
0002,66

Can I use CSV: Summarize rows with formulas to sum the Number column for each unique value in the ID column? If yes, what's the formula to use? To be sure, I'm looking for a result like this:

ID,Number
0001,168
0002,965

This should be easy to do with a batch/the below sequence, but not as efficient:

Tables: Create table for each unique value
Tables: Start Workflow for each row
Tables: Sum data in a column
Tables: Update a row

Best Answers

Answers

  • Thomas_937381Thomas_937381 Posts: 196

    Thanks, @Dylan_478989! This is an awesome pattern that many will be able to benefit from. :)

    For anyone looking to reuse the pattern: just keep in mind the size of the dataset you're looking to summarize. The JSON and comma-separated values text will need to be within field limits.

  • Thomas_937381Thomas_937381 Posts: 196

    @Dylan_478989 Is there a simple way to modify this to count the rows against each one of those unique values? For example:

    ID,Status
    101,Pending
    102,Pending
    103,Approved
    104,Pending
    105,Processed
    106,Processed
    

    I'd love to have something like this, but suspect the parseInt goes away. What should be used to simply treat the value as 1?

    Status,Count
    Pending,3
    Approved,1
    Processed,2
    
Sign In or Register to comment.