Getting specific values from a collection of columns

[JWebb]

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?

[Aswin]

I think the Unpivot node can help here.

[ipazin]

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:

Replace Values over Multiple Columns – ipazin

Br,
Ivan

[JWebb]

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