fill out missing value by another list

I have two input tables, 1 is a list of fruit, and 2 has 3 columns of fruit with some missing values on each row. I want to make a table with combinations of the fruit under the constraint of table 2. I try to find all combinations of input table 1 with cross-joiners to itself but get “out of memory error” as the list is huge. If I fill out the missing values under the constraint of input table 2, hopefully, the task is smaller.

Input table 1

set
[apple, banana, orange, pineapple]

Input table 2

fruit1 fruit2 fruit3
apple banana
banana pineapple
orange banana

Output table

row fruit1 fruit2 fruit3
row0 apple orange banana
row1 apple pineapple banana
row2 banana pineapple orange
row3 banana pineapple apple
row4 pineapple orange banana

On output table. row0 and row1 are the combinations under the first row constraint of input table 2 (orange and pineapple for the missing value on first row), row2 and row3 are the combinations under the second row constraint of input table 2. Row4 is the combination under third row, another combination “apple” is removed as it is the same as row0.

Hi @anguslou

See this workflow.
fill out missing value by another list.knwf (100.3 KB) I needed quite a few nodes to achieve the desired output. And although I used a loop (@bruno29a), it works, at least for this example. Hopefully also on your bigger dataset as well.


gr. Hans

4 Likes

Thanks. I will try and feedback.

1 Like

I borrowed @HansS great solution just to try a different way using crossjoiner, column agg and ungroup.
We would need to rename the header but at a first glance it seems to work

br

1 Like