Best way to summarize arrays

1

Hi,

2

Hi @Vladimir_V_Bauklien
and welcome to Coda Community! :handshake:

This sounds as a Map/Reduce issue.
Before trying to find a solution on an array-based context, a quick question: is this the actual case or you have a more table oriented structure?

Perhaps it could be tackled in a different way, if that is the case: let me know.

Thanks!

1 Like
3

Thank you for question, @Federico_Stefanato

Here I arranged more table oriented view of that problem (Previously on “M?” subpage) - I guess it may be resolved with some elementary trick, just cant get it yet.

In short, we get a table with Spotify orders, each of which consists of several large entities, for example, order #1234 consists of three P and two U, and order #4231 consists of one T, one P and two U.

Each large entity means different amounts of several small entities, for example, P contains 1 t and 3 c, and U contains 13 c, 5 d, and 23 a.

All we want is to count the total number of all the small entities for each order.

4

OK @Vladimir_V_Bauklien I have solved this puzzle by creating a table that expands each array into the table by making each row resolve list().nth(thisrow.rowid). I do this for the part names and for their quantity.

Then I have a column that brings in all the matching parts from the table, finding duplicates with:
thisTable.Filter(Part=thisRow.Part)

Then I Sum() the array of matching part names to get the total for each part.

To dedupe, I create a checkbox that determines if the Row ID, if there are multiple rows for the same part name, is the lowest one (a proxy for uniqueness).

Then I create a new view of this table, filtered to only show the lowest row ID:

935×779 44.8 KB

I did this in the ‘JA Solution’ page of the doc you shared.

2 Likes
5

Thank you so much, @Johg_Ananda, this is definitely the solution.
Trying to figure out how it works and how to relate it to the order numbers if there are more than one.

6

:pray: You’re welcome! Glad it worked for you :slight_smile:

what do you mean?

1 Like
7

Dear @Johg_Ananda,

I didn’t even think about the “rowID”, you showed how important it is to develop skills to find solutions that are not visible at the surface and that the “rowID” is part of the solution!

Really impressive how you mapped step by step :footprints: towards a solution. :diamond_shape_with_a_dot_inside:

Thank you very much sharing this :star: :star: :star:

2 Likes
8

Hi @Vladimir_V_Bauklien ,
full credit to @Johg_Ananda for his solution that I think just works great.

I took some time to review a bit the data model and the underlying logic to provide an alternative solution asking myself “what I would have done?” (see in the FS Solution).

I don’t know if this matches your needs, but I feel that some changes in organising data could help a bit.
Anyway, feel free to ask if it’s not fully clear.

Cheers!

3 Likes