Adressing the last Column..

Hey guys,

I just started with knime and want to search through 500 Excel files which have more or less the same form. I want to go into every excel and copy specific cells into another excel. My solution works okay but I got one big issue I cant resolve.
I have a column which consists of “io” and “nio” values. But due to the excel sheet macro which created the sheet somehow managed to put the io/nio values into Column 15, 16 or 17
But since a macro edited the sheets I have one fixed information:
The io/nio values are always in the last column available in the sheet.

(I cant really do something about it and need to figure it out. The whole task would be searching throug about 5000 excel files).
I tried using a Rule based Row Filter. The moment the Node wants to adress Col17, but the correspondig sheet has no Col17 (e.g. the io/nio stuff is in col15 and thats the last column in the sheet there literally isnt a col17) the program stops and exits.

Is there a way I could adress a filter and tell it: Always use the last column available and take all rows that have io/nio in it?

Thanks & regards
Bastian

Look if this node can help you.

1 Like

Hi there @bkaiser,

welcome to KNIME Community!

as @izaychik63 suggested you can extract table dimension (which gives you number of columns) and then build logic to do what you need with last column values. Also you can try Column Expressions node where you can automatically access last column with this following syntax:

column(numberOfColumns()-1)

Column Expressions is based on JavaScript syntax so you can wrap above expression into if/else clause for example to output 0 or 1 in new column that will always have same name or even better. You can just rename last column to always have same name and then use it in a Rule-based Row Filter node :wink:

Br,
Ivan

Br,
Ivan

4 Likes

I dont see the Column Expressios in den Node Repository…sorry for this “noob” question - I am quite new to Knime :slight_smile:

Hi @bkaiser,

did you installed the knime extensions? If not please install it.

If you have installed it, you can use the search field of the Node Repository to find the Node.

Demo

BR Hermann

My firm just has Knime 3.6 so I cant just drag and drop it in Knime - or is there another way on installing it?

Hi @bkaiser,

3.6? Wow! When is update planned? Newer version bring cool features and nice improvements :wink: Also you are lucky as Column Expressions is available from version 3.6 :smiley:

https://www.knime.com/whats-new-in-knime-36#column-expressions

So one way to install additional extensions/nodes is following. From within KNIME go:

File--> Install KNIME Extensions...

Then for Column Expressions under KNIME Labs Extensions find KNIME Expressions extension:

ColExpress

Hope this helps!

Br,
Ivan

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.