Dear all,
I have two tables:
Table a
a1 a2
10
20
Table b
b1 b2
10 a
10 -
10 null
10 b
10 c
20 a
I have to fill field a.a2 with values from b.b2
I can join a.a1 to b.b1
Then I need to add all values b2 where a.a1 = b.b1 except if containing null and “-“ separated by ; into a variable.
Every value from b.b2 should appear only once.
Results for my examples
a.a2 for a.a1 = 10 = a; b; c
a.a2 for a.a1 = 20 = a
Any idea is high appreciated 
Thanks for help!
BR,
Heinz
Hi @Heinz
In your data I wasn’t sure if “null” meant missing, or the literal word “null”.
One approach I might take to this is to first of all transform your lookup table (Table B)
I would use the Rule-based Row filter to easily exclude the “b2” rows that you don’t want included. These are the rows containing “-” and null.
The rules could be like this, and the first line allows for null meaning “missing”, whilst the second rule includes “null” as a string literal.
MISSING $b2$ => FALSE
$b2$ IN ("-","null") => FALSE
TRUE => TRUE
You can adapt this as you need but the resultant table is this:
After that, you actually want the lookup values to be unique-concatenated so I’d use the Group By node:
Group by column B1, and set the delimiter to “;”. I told it to retain row order. Is the ordering important? (If you want the items sorted prior to concatenation, sort your table on B2 using a Sorter node first.)
Configure it to perform a Unique Concatenation on Column b2. You could also exclude missing values here, but we already filtered them out with the Rule Based Row Filter.
Then use a Value Lookup
Configure this as follows:
This will result in the matching contents from b2 appearing against your Table A data., which you can merge with column a2 by using the Merge Columns node, specifying a2 as primary, b2 as secondary and replacing primary/deleting secondary
Note that in my example below I’ve added to the data in table A to demonstrate what happens if the a1 value does not appear in table B, and is already populated (nothing!)
You could use a joiner node in place of the Value Lookup, and then choose the columns to keep, and tell it to retain matching and left unmatched rows, but the rest of the workflow would be similar. That’s really a matter of personal preference and whichever you find simpler to use in this situation.
Hi Takbb,
thank you very much for your support! Brilliant!
BR,
Heinz
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
