Excel Nested If

KNIME Analytics Platform
1

How to breakdown this excel formula using column expression or rule engine in knime?

=IF(OR(F32=“100003000”,F32=“105001000”),TRIM(F32&“-”&LEFT(P32,10)),IF(OR(F32=“115001100”,F32=“230004600”),TRIM(F32&“-”&I32&“-”&M32),IF(OR(F32=“115002000”,F32=“199010000”,F32=“199012100”,F32=“199013000”,F32=“199013080”,F32=“230004800”,F32=“270001000”,F32=“270006310”,F32=“270007100”,F32=“299001000”,F32=“115004300”,F32=“115001300”,F32=“230001200”,F32=“500011200”,F32=“720000700”,F32=“720000900”),TRIM(F32&“-”&I32),TRIM(F32))))

2

What have you tried?

It looks like a job for the new switch function in expression node.

Have you given that exact formula to K-AI?

1 Like
3

Hi @charabor1 , I think the first step is to make the original excel formula readable. Eg

=IF(OR(F32="100003000", F32="105001000"),
    TRIM(F32 & "-" & LEFT(P32, 10)),
    
    IF(OR(F32="115001100", F32="230004600"),
        TRIM(F32 & "-" & I32 & "-" & M32),
        
        IF(OR(F32="115002000", F32="199010000", F32="199012100", 
              F32="199013000", F32="199013080", F32="230004800", 
              F32="270001000", F32="270006310", F32="270007100", 
              F32="299001000", F32="115004300", F32="115001300", 
              F32="230001200", F32="500011200", F32="720000700", 
              F32="720000900"),
            TRIM(F32 & "-" & I32),
            TRIM(F32)
        )
    )
)

(code reformatting courtesy of chatGPT :slight_smile: )

I will assume that although the cell references includes the row number, for migrating to KNIME we are interested only in the column name, and so for the example, we can refer to it simply as columnF, columnM, columnI and columnP. You will need to change this to reference you actual columns.

Since the formula involves concatenation and trimming of the output, the Rule Engine node is unsuitable (since it allows us only to output single literal values or column values. It does not allow any manipulation of the output.

Therefore the main choice of node for replicating this is Column Expression, String Manipulation, Java Snippet and (in KNIME 5.3 onwards) the Expression node.

To achieve the above with String Manipulation, the code would be something like the below - no guarantees, as I have no data to test it with):

string(
	
($columnF$.equals("100003000") || $columnF$.equals("105001000"))
? strip(joinSep("-",$columnF$, substr($columnP$,0,10)))
: ($columnF$.equals("115001100") || $columnF$.equals("230004600"))
  ? strip(joinSep("-",$columnF$, $columnI$, $columnM$) )
  : ($columnF$.equals("115002000") ||
     $columnF$.equals("199010000") || 
     $columnF$.equals("199012100") ||
     $columnF$.equals("199013000") ||
     $columnF$.equals("199013080") ||
     $columnF$.equals("230004800") ||
     $columnF$.equals("270001000") ||
     $columnF$.equals("270006310") ||
     $columnF$.equals("270007100") ||
     $columnF$.equals("299001000") ||
     $columnF$.equals("115004300") ||
     $columnF$.equals("115001300") ||
     $columnF$.equals("230001200") ||
     $columnF$.equals("500011200") ||
     $columnF$.equals("720000700") ||
     $columnF$.equals("720000900"))
    ? strip(joinSep("-",$columnF$,$columnI$))
    : strip($columnF$)
)

I’m exhausted :wink: I’ll leave it to others to give examples with the other nodes. For clues about how the above code works, see this post.

See also:

4 Likes