How to dynamically set the number of columns within the split to columns tool? [Macro]

[DanAAC]

Hi community,

I was hoping to find an answer for my question.

Say I use the split to columns to split column X and I want the number of columns to be dynamic. How do I achieve this?

In my example the largest number of colors is 4, but let's say my database contained more colors and I don't want to manually check what the largest number is for the split.

How can I use the macro tools - control parameter, action tool, macro input, etc. to take the dynamically split the columns based on the largest number?

Thanks!

[Noah-Waite]

Depending on what your intended output is, you may find a solution similar to what I've attached helpful. This helps avoid the interface tools altogether. I created the additional text input so you can test how it handles additional colors and/or car manufacturers.

[Noah-Waite]

Another option that retains the format of your original output.

[DanAAC]

@Noah-Waite Hi Noah, thank you very much!

It all looks great; I just specifically look to use the macro tools to code the number of columns to split into the split to columns tool.

Have you worked with the macro tools before?

[Tofel]

All you need to do is to create any numerical interface input and connect it to the top of text to columns tool. This will create 'Action tool', where you need to click on the 'NumFields' section. There is no additional input required here. Just make sure that 'Update Value (Default) is selected as action on the top:

Outside of macro you can select number of columns you would like. I also added delimiter change, so you do not have to go inside a macro to change it.

 

I am attaching workflow with a macro. I also created version without macro, which is much better in my opinion.

Automatically coding number of columns might be difficult, because you would need to run the workflow once to determine number of required columns and then second time to apply it. I am not even sure whether this is possible. Just use cross tab. The simplest options are usually the best.