Columns comparison

Hi,

I have two array columns and I have to look if an element of the first one is included in the second one. I tried different solutions and the best one is the node "Column Aggregator" with "Intersection" as aggregation setting but in the case of an empty array, it returns the value list of the other one, while I would like to have results only if a value is included in "both" arrays. I also tried the "Rule-based row filter" with the operator "IN" but it does not work, it returns an empty table (example: $Col2_SplitResultList$ IN $scores_Arr[0]_SplitResultList$ => TRUE).

Thank you in advance

-Gianluca

Hello Gianluca,

thanks for reporting the issue with the intersection method and missing values. It shouldn't return any elements if one of the two columns is missing but also a missing. We will fix this with the next version.

As a workaround you can use the java snippet (see attached workflow).

Bye,

Tobias

Hi again,

I have to compare 2 columns from different tables but one is a collection of element (I have it as string and a list from the Cell Splitter node) (example: a vs. a;b;c;d => TRUE).

Any suggestion how to do this? Joiner and Reference Row Filter nodes do not work with list and return only the perfect match (a vs. a;b;c;d => FALSE)

Thanks

-Gianluca

Hello Gianluca,

If you have a single element and you want to know whether this element is in a list you can use the Rule-based Row Filter (or any Rule node, depending on what you want to do if the element is included).

$column1$ IN $column2_SplitResultList$ => TRUE

Best,
Ferry