A set complement that preserves all columns in first file

[Nazaire]

Hi all,

I would like a slightly different of Set Operator (Complement) that would return not only difference keys but also the corresponding rows in the first file.

Here is an example. Suppose I have files File1 and File2 that both contain a column, say Key, in a way that the difference of these columns is nonempty.

I would like to retain the rows in File1 whose Key lies in the difference of File1 and File2 according to Key. In this case, a Set Operator (Complement) node generates

Ultimately I want to retain the rows in File1 whose key lies in the complement, i.e.

One way that I thought about it is, as above, to generate the complement and then use it as a kind of dictionary, but I do not know how to retain the rows in File1 whose key appears in this dictionary. There may be another way.

Any help will be appreciated!

[aworker]

Hi @Nazaire

A -Joiner- node should do the job here where joining is done based on the “key” columns from both files. In the node configuration you need to select a full join operation and activate the three outputs into separate output ports.

The second or the third output port should provide you with the expected result depending on which order you connected your tables from the files.

Hope it helps.

Best

Ael

[Nazaire]

Thanks, just what I needed.
Cheers!