Column Name (Regex) in a loop

[kuenzifr]

I have a series of columns with various generic names which I need to rename based on a translation table. For example columns
“Primary stuff 1”, Primary stuff 2" …, “Selectivity stuff 1”, “Selectivity stuff 2”… become
“Client A stuff 1”, “Client A stuff 2”…, “Client B stuff 1”, “Client B stuff 2”…

There can be variable numbers of columns (“stuff”).

I tried using:
Table Row to Variable Loop Start (connected to translation table)
Java edit Variable (convert values in each row to regular expressions)
Column Name (Regex)
Variable Loop End

However this only seems to run the last row of the translation table. How can I get it to run through all of the rows of the translation table? I presumed that “Variable Loop End” would be the appropriate end node for a “Variable Loop Start”.

loop test.knwf (15.5 KB)

[gonhaddock]

Hi @kuenzifr

It results a bit messy, but looping over rows sometimes is easier…

20210726_loop-test.knwf (45.7 KB)

Regards

[bruno29a]

Hi @kuenzifr , you don’t necessarily need to loop here.

Please table a look at the Insert Column Header:

Insert Column Header

You can have a mapping table that you can use to replace your Column Header.

I put a quick demo for you, and it looks like this:

Original Data table:

After renaming based on mapping:

And here’s the workflow:
Rename columns based on mapping.knwf (8.2 KB)

The interesting part here is that, you can request from the data owner to give you the mapping in csv or excel when you are getting the data, and you can simply plug the mapping via a File Reader or CSV Reader or Excel Reader, and the rename would be done according to that mapping.

The Column Rename (Regex) is an alternative too, if there are a lot of columns with similar patterns, but the downside of it is that you have to loop through the different patterns, and you may have conflicts during the matching process. For example, first iteration will rename all columns matching the first pattern. Some columns have been renamed based on the first pattern. If these have somehow been renamed with some name that matches the other patterns, you would be renaming these columns several times - obviously it will depend on the patterns that you choose, but I cannot see these patterns as you have only mentioned an example.

[kuenzifr]

Thank you for the tip bruno29a. It turns out that I don’t need to use the looping at all.
I put together a series of nodes to do this, with the “Insert Column Header” you suggested putting it all back together again.

[bruno29a]

Hi @kuenzifr , no problem, happy to help.

That’s correct, as long as you get a mapping table (which you created created through a few nodes), you can just replace all the column headers based on that mapping table as I demonstrated in the workflow.

[Daniel_Weikert]

And if you don’t want to build a mapping table yourself then you could extract column headers, transpose, do the regex replacement (or use string manipulation) and then use a bruno suggested the insert column header
br