I needed a single formula (not a Button) to transpose the Rows and Columns of a table.
Max
7 Likes
How It Works
each line explained…
- the List() produces a list of lists or 2d matrix of the table contents
- we call that matrix V so we can refer to it below
- for J=1 to NrCols - Count(V) is the number of columns
- for I=1 to NrRows - Count(V.First()) is the number of rows
- build a new matrix that is transposed - V[I,J] is in row then column order
- next I
- next J
- traverse the transposed matrix rows the columns
- pad-out the cell and make it fixed-size-font, end with a newline
The resulting display is the contents of the Original table with the rows and columns transposed
Max
7 Likes
This is awesome!
Thank you so much for all of your contributions to the community. Your posts are always so insightful and are true gems for Coda enthusiast.
2 Likes
ok… i looked at my code and realized it was twice as long and complicated as needed.
so i have reduced it to a more manageable minimal set of code.
here is the result (as before, its a ‘virtual’ transposed table - display-only)
and here is the shorter formula…
How It Works line-by-line
- the List() and WithName() collects the entire table into the 2d matrix M (a list of one-list-per-column)
- for C=1 to NrColumns (Count(M) is the number of cols)
- for R=1 to NrRows (Count(M.First()) is the number of rows)
- show each cell M[C,R] padded and in fixed-width-font
- next R and separate the cells with a space
- next C and separate rows with a line-break
- end the withname for M
This runs faster, has less moving parts, and is easier to follow.
Someone asked why the _Code()? it shows the cells in a fixed-width font. otherwise the width of each cell would be slightly different and they would not align properly.
Max
6 Likes
@Agile_Dynamics, one would think I no longer would be surprised by all the gems you publish, and yet, one’d be wrong.
This is incredible, thank you SO much for so generously sharing your findings with the community.
In order to make your wondeful contribution accessible to as many interested readers as possible, I have taken the step-by-step overview you included and broke it down into displaying the formula results for each step.
It helped me much faster understand what your code does, and I’m thinking it might serve the same purpose for others.
I’m sharing the doc in an fully editable view, so that everyone can peek inside the formula editor - please do make sure not to delete anything, as this would sour the experience for others.
Max, what a community champion you are, indeed. Thanks so much for all you do for our Coda community!
All the best from Austria,
Nina
4 Likes
thanks @Nina_Kastenauer1,
you did a great job breaking it down, explaining it well, and displaying all the intermediate results.
much appreciated
max
1 Like
Recently, I was searching for a way to dynamically represent data in a virtual table (that is, moving from a “one-to-many” relationship to a “one-to-one” relationship without the user having to press a button to achieve this.
@Agile_Dynamics graciously took the time to share his wisdom with me on how to best achieve this. The second thing he said once I finally cracked it (after “Congrats!”) was “Now you must share your solution with the community”. And, as is usually the case, he was right of course. So, here goes:
Imagine we want to schedule a team retreat. While the scheduling is under way, we want to dynamically keep track of the number of times each team member interacts with every team member.
That is, we want to dynamically visualize Interactions Scheduled, ie how often does each team member meet with every other team member?
We will create the virtual table in the following two ways:
And here are the formulas used (I decided to include a static header, as well as a static side-bar, to ensure that the 2D-Matrix could easily be read by the user)
And to display as colored circles, simply add a “Switch-If” to the formula to display a green circle to represent the value 1, a yellow circle to represent the value 2, etc.
Enjoy,
Nina
4 Likes
Yet again I needed a sinple formula to display a TABLE in a TRANSPOSED format (ie swap rows and cols)
And I found an even SIMPLER solution…
- build a 2d matrix (list of lists) of the table columns
- loop through each column with foreach()
- loop through each of the col’s cells with foreach()
- pad out the cell to a fixed size using rightpad()
- use a fixed-width font using _code()
- split the result into rows using bullededlist()
2 Likes
In case anyone is interested, I have figured out how to create, modify and display vistual tables in Coda.
Virtual Tables
Sometimes it is useful to represent a table as a matrix; M, aka, list of lists.
Formulas can address arbitrary values using
M.nth(I).nth(J)
We also can use the Apache Parquet form for virtual tables. Where the first column is the row-identifier and the last column is a list of the column names. This is used in ‘big data’ applications to load large SQL tables into memory for faster processing.
We have done this in Coda as follows;
Create a virtual table V from a coda table P which has columns Product, Size, Quantity, Price…
Which produces a list-of-lists like this…
We GET the cell for row & col as follows (where row=id of the row, col=name of the column) as follows;
- first it finds the index of the column using find(col) on the list of column names
- then it finds the index of the row using find(row) on the column-list
- finally it uses the nth() formulas to return the cell value
We can MODIFY a cell for row & col using the splice() formula as follows
The result is a new virtual table with the single cell at row & col changed to value
Note that this is NOT an action formula. So it does not need to be in a BUTTON or AUTOMATION.
We can manipulate virtual tables inside ordinary column formulas (or canvas formulas).
The resulting virtual table is updated immidiately without the need to click a button.
We can display the virtual table using this code…
Which looks like this;
I have made a pack for all this (and many more operations) for my clients.
If anyone is interested, I can get permission to publish it to the gallery.
Max
6 Likes
what a wonderful contribution @Agile_Dynamics , i am looking forward testing it myself by following your steps. This contribution also speaks to me because I believe we need this kind of transpose logic supported by coda with functions. For now this is an interesting way to move forward, merci!
1 Like
The pack sounds like a great option for quick application.