Dynamically Include/Exclude Fields

[hellyars]

I am working with the Field Info tool.  A long setup is required....

My inputs are Excel files in a data across construct.  No big deal there.

The first 13 field/names (dimensions) remain constant for each file. 

The last 3 fields/names (dimensions) also remain constant for each file (with the last field being FileName).

In theory, there should be 6 fields that contain values (measures) starting with field #14, for a total of 22 fields.

In reality, the number of fields that contain values (measures) can range from 6 to 28 fields -- for a total of 22 to 44 fields.

The value fields represent quantity and dollars for a given year across three years which results in the minimum number of 6 value (measures).

The names of the value fields are different for each file, but follow the construct:

Fields 1-13	Yr 1 Quantity	Yr 1 Dollars	Yr 2 Quantity	Yr 2 Dollars	Yr 3 Quantity	Yr 3 Dollars	Last 3 Fields

The challenge occurs when Yr 2 Quantity Yr 2 Dollars are not finalized. 

This generates additional Quantity and Dollar field pairs for Year 2 -- up to 10 pairs or 20 fields in total for one input file.

Typically even this is not a problem as I can tell the workflow to simply to grab the last quantity / dollar pair for each Yr. 

The example below depicts three pairs for Yr 2 Quantity and Yr 2 Dollars. 

Normally, I would tell the workflow to grab the last two fields for each year, which in this case would grab Pair C fields for Yr 2.

Yr 1 Quantity	Yr 1 Dollars	Yr 2 Quantity Pair 1	Yr 2 Dollars Pair 1	Yr 2 Quantity Pairs 2-9	Yr 2 Dollars Pairs 2-9	Yr 2 Quantity Pair 10	Yr 2 Dollars Pair 10	Yr 3 Quantity	Yr 3 Dollars

**The problem occurs when I don't want the last pair for Yr 2 for a given file.  What if I want Pair 7 of 10?

Is there a way to dynamically select which fields to include/exclude for a given file input using a look up table or some other approach?  ...that is to take the first 13 fields, the last 3 fields, and then using some approach to tell it which value field pairs to take?

[apathetichell]

Dynamic Select allows you to choose which fields you want. You can add it to a batch macro and use an [Name] in ... to select which you fields you want.  you can also use regex_match if you have specific requirements that you can dictate using regex.

[alexnajm]

Have you looked at the Dynamic Select tool? You can use a Formula in here to select based off of Contains functions, column positions, and so on!

[hellyars]

@alexnajm @apathetichell 

Thanks both.  I resorted to brute force.  It is not pretty, but it works. 

The Dynamic Select tool was very helpful, but I found myself wanting something that combined the functionality of both the Dynamic Select, Field Info, and Sample tools.

For example, field selection is driven by an a year date included in the filename -- hence I had to use a Field Info tool coupled with a Regex tool to create a new field with the year that would then drive how I identified the field pairs of interest.