pls send help - if function

[Pippobaudo89]

Hi there

i m very knime beginner. I would like to know if exists a node that if it reads A in a ROW transofrms it in B. For example: with an excel reader there are 100 ROWs with many values (1,2,3,4…100) and i d like to know if exists a way to read 1,2,3,4…100 and give me the output A,B,C,D…CA. I try with a string manipulation but it doesn t work…
Anyone could help?

sorry for my bad english…

Thanks in advice

[takbb]

Hi @Pippobaudo89 , and welcome to the KNIME community!

I think you are asking for a mechanism to transform numbers into letter sequences, (similar to Excel column names)

I’m slightly confused by your example, since 100 would be “CV” and not “CA”.

I don’t know of any node that could do this directly, but the Column Expressions node can be used for this. I don’t know if Column Expressions is installed as standard these days with KNIME, but if not, you can install it by dragging from the above hub link.

To achieve the result you require, you need to iteratively calculate the number modulo 26, to derive each corresponding letter, until your number reaches zero. A column expressions script for doing this is:

n=column("column1")
alpha="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
letters=""
while (n>0){
    m=mod(n -1,26 )
    letters= substr(alpha,m,1)+letters 
    n= floor((n-1)/26)
}
letters

This calculates each letter in turn by finding the current value of (n-1) modulo 26, and then on each iteration dividing (n-1) by 26 (using just the integer portion) and keep going until n reaches 0.

I generally try to avoid Column Expression nodes as they can be a little slow on large datasets. An alternative would be to write the same using a java scripting node which would probably execute faster.

Please see the attached workflow.

Transform Numbers to Letters.knwf (7.0 KB)

I hope that helps.

[Pippobaudo89]

Thanks so much

i’ll try to explain better (but probably you already answered).
I would like to transform when i read a excel one value to another value. If i read CAT in column B row 1, i woould like to automatically transform CAT into CAT1. Maybe it could helps prepare a transcodific table with the value like you posted above. But i need multiple tables for each column to transform multiple value in symoultanous time…

hope you can understand.

thanks for helps

[takbb]

Hi @Pippobaudo89,

Unfortunately I’m afraid I am not sure now what you are asking as your new example appears to me to be very different from the first example you gave.

Yes I think you really need to put together a sample table or tables of values and give examples of the required outputs (with the rules explaining the transformation you are looking for) so that either I, or somebody else, can assist further.

[Daniel_Weikert]

I think @takbb proposal is certainly right. If you like to add numbers to your values based on the row you can check out counter generation node (maybe you need string manipulation as well to concatenate the two columns). If CAT is in Row 12 you can create CAT12 automatically like that
br