Dynamically identify column based on value contained within

BMB13

Hello All,

I receive approximately 150 sets of data where I need to pull 4 specific columns, three of which are always the first three columns and a fourth which varies for each dataset, i.e. it may be the 4th, 5th, or 10th column, with no easy way to identify which column that will be. The only way to identify which column is the fourth is that it will always be titled "Label", but the title occasionally appears in the  first, second, third, fourth, or fifth row (i.e. if I dynamically rename or skip n number of rows, the "Label" indicator may or may not end up in the first row.

Currently, I am using Dynamic Select to select the first columns

([FieldNumber] = 1)
OR
([FieldNumber] = 2)
OR
([FieldNumber] = 3)

I would like to add something like the following to identify the fourth column by scanning the contents of each field, and selecting it if any of the field values = "Label" along the lines of 

OR
Contains([ANY AND ALL FIELDS],"Label")

but don't know how to identify the underlined portion, which is obviously not a field name.

Any ideas?

Thanks!

Claje

Doing this kind of dynamic selection can be a little tricky.  The best way I have found to accomplish something like this is to leverage the Dynamic Rename tool to populate the "Description" for fields you want to keep, and then use this to filter.  You can identify which fields you are keeping via a Sample tool (to get only the first few records for each file), a Transpose tool - to flip the data for easy filtering, and a Filter tool, to look for the fields you want to keep.

I've attached the workflow here.

There's also a "Synthetic data generator" which will dynamically create up to 5 records for a set of files, and dynamically distribute the "Label" for each file to a random row(1-5) and a random column (4-10), but I have this disabled.  You could use this if you wanted to test different configurations.

I hope this helps explain the easiest way to do this kind of data-driven selection!

claje_dynamicSelect.yxmd