the title says it already: Is there a way to dynamically add columns to the Column Expressions Node?
What a colleague of mine is doing is heavy formatting of a report using the Continental XLS Formatter extension. He uses the Column Expressions Node to apply the tagging rules.
But within that report there are some columns that might be different every month. Like Products for example. One month we have the 3 products A, B and D in there, next month it might A, B, D, E, J, K products.
We only know the “fixed columns” so the ones that will always be there. I recommended to set up a Case Switch Node based on the info if a column is one of the known columns (aka the fixed ones). If it is not, it must be dynamic and then the workflow runs through a different loop.
I wonder if there is a better solution than my suggestion?
Are they completely set on that approach? They could use this node instead and setup some column position based formatting (with empty column hiding) / conditional formatting on the other end… It is the way that I prefer to do this kind of thing now, but everyone has their own comfort areas. I was a heavy excel guy for a long time, so this is an infinitely faster / more portable / more flexible approach for me. I like a good presentation. I can point it to a standard template, save hours of formatting and end up with a much cleaner end result.
@kowisoft how about two branches. One for the stable columns one for the dynamic ones. To deal with the column names in the Column Expressions Node you could do a loop that would:
rename the (dynamic) column to a temporary name “_temp_column_xyz” then do the transformation with this column (the name will always be the same for the expression) and then rename it back to the original one. You could only send this column wise to the loop and collect the results at the end. Not the most elegant way but once you have set it up it will be dynamic.
Thank you for this interesting node. Is it comparable to the KNIME native Excel Cell Updater Node? I don’t get how it determines in which cell in the Excel template the data should go…?
I am also not sure if this works, as some part of the columns is not fixed, so it could be e. g. 1 columns, 3 columns or 10 columns.
They are not really missing but rather dynamic (aka, could be 1, 3 or 10 columns).
Aah, thank you @mlauber71 - I didn’t think of giving them a dummy name and then overwriting this later on (although I already did this in another use case). I guess this is the way, I will suggest my colleagues now.
I would love a “Cell Updater version of this node @AnotherFraudUser !
Here is how I make this node work for a use case like yours @kowisoft.
I use a second tab that contains “control settings” for the conditional formatting rules used on the table. Think about it like Dynamic Conditional Formatting…
It can easily handle a dynamic number of columns, KNIME can dynamically control the column references that drive column based conditional formatting rules. Start with a clean standard basic formatting for the table, and then only override where necessary via conditional formatting.
You can also control things like conditional highlighting of rows / cells via these control settings, separate column containing “highlighting flags” generated by KNIME, or based on user interaction.
You can also write in formulas from KNIME for additional flexibility.
This “control settings” approach to conditional formatting really allows for a lot of flexibility for a few templates to handle a variety of use cases. Saves crazy amount of time and users appreciate visual feedback on their actions.
@iCFO what do you mean exactly - cannot quite follow
The normal excel to template node should not care if additional columns come in - it will write them to the template file just in the same order as the input table.
For turning a knime table to the Excel Updater format I use
I wasn’t interested in a Cell Updater version of this node to solve the specific problem on this thread. It would just open up a ton more opportunities in general to be able to piece together a more disjointed presentation via a Write to Template Cell Updater node! We could really dial in some legit interactive dashboard style presentations.
That use case sounds like a job for the pivot node Pivot Table — NodePit
That node does the job of adding the required columns for you.
Then you may apply a concatenate the fixed part with the pivot table.
awesome that it worked for you @gfginja and welcome to the forums
I am super amazed of the AF Utility nodes as these free me from having to pivot my data into a values and a address column when using the Excel Cell Updater Node.
