Dynamically selecting columns based on another column value

[Genevieve]

Hello,

I am trying to dynamically select columns of which the first 2 digits match the value of another column in a neat way.

In the example if the code is 03, then select the default columns Name and tbd. Where the code equals 04, select: name, tbd and columns starting with 04.

The render report in the attached workflow illustrate what I don't want (too many columns in the final table)  and too many filters to reach the result). Mulltiple filters are the only way I could think of so far but it is not a neat solution as I need many reports from that data.

[danilang · Accepted solution]

Hi @Genevieve 

Here's a solution that relies on the fact that the outputs from each iteration of a macro are unioned before being sent back to the main workflow

Main

The main program uses  a Summarize tool to pull out the unique codes in the data set to pass to the Control Input of the macro.  The full data set is passed to the data input

Macro 

Inside the macro, set up as a batch macro, I simply copied your reporting controls and added a filter Tool to extract the rows needed and a Dynamic Select to select the columns.  The Control parameter takes the list of Codes passed from the main program applies them one per iteration.  The "04" in the filter tool is changed by the Action tool to match the Code of the current iteration.  In the Dynamic Select, the formula I used is 

[name] in ("Code","Name", "Tbd") or startswith([name],"04")

Like the previous Filter, the second Action tool replaces the string "04" in this formula with the current iteration code, First "03", then "04", then "10", etc.  The results of the row and column filtering process are passed to your reporting tools and each snippet is added as another row to all the snippets from the previous iterations.  

After the final render in the main program, the results look like this

Dan

[Genevieve]

Many thanks @danilang 

A very neat solution!

I knew I could count on the community.

Genevieve