Getting specific values from a collection of columns

I have come across another issue! I have a database created generally by a team not attuned to data analysis and we have various codes in an array of columns. I am looking to extract certain codes into one column (so I can do a lookup) but I can’t figure out how to do it. Below is an example of the kind of table I have:

ID Column 1 Column 2 Column 3 Column 4 RESULT
123 A1
456 A1 B2 B2
789 A1 A2 B22 B22
987 B303 B303
654 A1 C1 B4 B4

I am looking to extract all the B values (which do not have a consistent length!). I have considered a column expression, but I cannot figure out the nested IF statement in it so I don’t think this is the best way to go about it. I have also tried the column list loop but it only seems to work on one column.

Any other ideas on how I can do this without having eight separate nodes (there are 8 columns!) with eight separate column mergers. Would a collect column with a RegEx work?

I think the Unpivot node can help here.


Hello there.

agree with @Aswin. Unpivoting is standard trick you can use here. Follow it with LIKE operator from Rule Engine to determine where Bs are, then group it to get Result column and finish it up with Column Appender.


For above described logic you can take a look at this workflow example:



Thanks to you both! Why did I not think of that? It seems so simple now I see it!

1 Like