Selecting only the first and last columns from a month range that can be dynamic.

Question

Hi all. Trying to solve the below but not able to find a solution. Could anyone in here help figure out?

I have a table like shown in the screenshot, with months as column names.

The above example shows months from Oct-22 to Mar-23, but these months could span any periods (Jan-22 to Dec-22, or Jan-22 to May-23, etc).

I want to do some trends analysis by picking only the first and last month in the range. In this example, I want to consider just Oct-22 and Mar-23. Similarly for any range of months provided, I want to pick the first and last months. How could I do that?

phottovy · Answer

@SPetrie, nice use of the metadata! I never would have though of that.

SPetrie · Answer

There are probably going to be a few ways to do this. My preferred method is to use a field info tool to get all the fields and then use samples to grab the names of the fields I care about and tag them using dynamic rename to update the descriptions. Then a dynamic select to select the columns I identified to keep,.

Dynamic Column Select.yxmd

phottovy · Answer

Hi @aliensurfer ,

I'm sure there a ton of more efficient ways to do this but attached is the first way I though of. I made the assumption that the column names were formatted as strings in this example.

One of the tricks I used was taking advantage of the "Output Common Subset of Fields" option in the union tool.

Once you have identified the column you want to keep, you can use the union tool to drop all of the other columns. In this case, I feed a table with only headers (no rows of data) into the union tool and it drops all of the other columns.

Min_Max Dynamic Select.yxmd