Dynamic Field Selection which contains recent month

[learningcurve]

Hi All, 

I have a condition on field which I am unable to solve. I have 20 fields in a file, out of which I am selecting only 5. Names of these 5 fields keep on changing each month when new file comes in Example column name: ABC Market(10.31.2018) will change to ABC Market(11.30.2018) 

How can I create something in alteryx which can pick up next month's field automatically? 

Any help will be appreciated thank you

[DavidxL]

How about using the Dynamic Select tool?

[learningcurve]

Thank you for the reply David. There are many columns in my raw file which contain ABC Market(Date)

Dates go back to 6 months, though I just need to pick the most recent end of month date. 

Unfortunately this solution wont work for this problem. Any other suggestion? 

[DavidxL · Accepted solution]

There's probably an easier way to do this but here's a quick prototype. It does use a custom macro "Totally Transpose" which I've adapted from the community.

Basically:

1. use Field Info tool to list your column names.

2. Do some parsing to extract the date portion from the name so that you can sort by date and take the latest one

3. Filter that and other columns you needed

4. Using the Totally Transpose custom macro to flip the data  and use a join to "select" the columns before flipping back

[learningcurve]

Thank you, worked smooth with my current scenario. Learned something new today. Thanks David.