topic Re: Formula column where all row values are a certain column value matching another column in Discussions

Formula column where all row values are a certain column value matching another column

Feli — Sat, 10 Jun 2023 23:48:42 GMT

I have a table like this

and want to create a new formula column (reference size fruit) where the row value is the value to the Column "Size", but with the condition that the order is 5 and the value updates for each fruit type.

E.g., the result should look like this:

I can do it using a nasty workaround (marking 5 in column "order", select matching values, do a subset using selected rows and update the original table with this) or I could write a JSL script, but I would prefer a Formula column solution for compactness.

Re: Formula column where all row values are a certain column value matching another column

jthi — Fri, 20 May 2022 12:45:14 GMT

As no example data table (images won't really work as data tables), I wrote smaller table to use as an example.

The formula might look a bit complicated, but works with the example data

:S[Col Max(Col Min(Row(), :F, :o == 5), :F)]

but it should be fairly easy to understand if you split it into three different formulas (I have attached data table with these). Idea is that first you get the rows numbers per fruit based on if Order is 5 or not using Col Min(). Then you use Col Max() to get maximum value of that column. Finally use the row number to get value from :Size column.

Re: Formula column where all row values are a certain column value matching another column

Feli — Tue, 24 May 2022 05:44:41 GMT

It's magic! Thank you, that was exactly what I was looking for.

Sorry for forgetting to attach my demo table.

Re: Formula column where all row values are a certain column value matching another column

mikedriscoll — Tue, 24 May 2022 19:14:49 GMT

I don't have a better solution to offer, but I think this solution may cause incorrect results if the order == 5 for any of the groups is the first row of a given group. For example either if it is out of order, or if starts at 5. Depending on what the real data looks like, it may or may not be an issue, or maybe it could be solved with sorting etc.

Edit: This appears to work but I didn't test much.

stRows = Current Data Table() << get rows where( :F[Row()] == :F & :o == 5 );
aa = Associative Array( :F[stRows], stRows );
:S[aa[:F[Row()]]];