I have a table with multiple values per row. All of the values are strings, and there is a unique ID column too.

column1 | column2 | uniqueid |
---|---|---|

randomstring | wronglengthrandomstring | ABC123 |

randomstring | randomstring | DEF456 |

I need to verify the length, which I did with the String Manipulation Multi Column.

column1 | column2 | column1_transformed | column2_transformed |
---|---|---|---|

randomstring | wronglengthrandomstring | 12 | 23 |

randomstring | randomstring | 12 | 12 |

Then I tested whether that length was equal to the correct value of 12 using the Math Formula Multi Column node.

column1 | column2 | column1_transformed | column2_transformed | column1_transformed_suffix | column2_transformed_suffix |
---|---|---|---|---|---|

randomstring | wronglengthrandomstring | 12 | 23 | 1.0 | 0.0 |

randomstring | randomstring | 12 | 12 | 1.0 | 1.0 |

Next I used Column Aggregator to aggregate the results from the Math Formula Multi Column node into a List

List | column1 | column2 |
---|---|---|

[1.0,0.0] | randomstring | wronglengthrandomstring |

[1.0,1.0] | randomstring | randomstring |

In my real life example I have many columns per row, so if there is an incorrect value (such as â€ś0.0â€ť) I would like the column it occurred it to be identified, like:

List | Problem Column | column1 | column2 | unique_id |
---|---|---|---|---|

[1.0,0.0] | 2 | randomstring | wronglengthrandomstring | ABC123 |

[1.0,1.0] | {empty since there are no bad values} | randomstring | randomstring | DEF456 |

How can I:

- look across the row for any incorrect values of â€ś0.0â€ť (they may occur multiple times)
- pull the number of the column that â€ś0.0â€ť occurs in from the columnâ€™s header (in the exampe above it would be â€ścolumn2_transformed_suffixâ€ť)
- put that number into a new column (â€śProblem Columnâ€ť)
- keep the unique_id column, which I will need to join this string data back to the rest of the data

I am new to KNIME and have had several close misses with different tools, but havenâ€™t been able to put the whole thing together. I appreciate any advice or suggestions on how to retool this. Thanks in advance.