Removing Columns Based on Conditions

[alexgibson]

Hello,

I am trying to dynamically deselect last years columns when the first month of the new year is introduced. Here is the scenario:

When February 2024 hits, I will run a workflow with old data from 2023 and new data from January 2024 getting the columns below.

The Jan 2024 column depends on the Dec 2023 column to run it's calculations, so I cannot get rid of the previous months columns until after it fully runs through the Jan 2024 column. When the Jan 2024 columns is introduced, I wanted to deselect the previous months columns whilst keeping the Record ID, Name, and Jan 2024 columns.

Thoughts?

[Prometheus]

@alexgibson Can you please upload some dummy data so we can see what we're working with and what your expected output is?

[alexgibson]

Sure. The expected output will be below (also in attached workflow with the dummy data). Keep in mind that this January 2024 data comes in February 2024.

[Prometheus]

@alexgibson If it doesn't have to output exactly like you specified, try this solution. I transposed the data and took the year out of the date and put it in its own column. I added month numbers to keep the months in order, then I used Cross Tab to pivot it back. After that, I removed the month number from the field names and was left with a column for every month and a column for year to distinguish between the two. If the output does have to look exactly like your expected output, we might have to use some type of macro to come up with a solution.

 

[alexgibson]

Unfortunately it has to be in that format. A Macro could be the way forward. I was also thinking that a Dynamic Select tool would work if I could specify what columns to get rid of and when.

[SPetrie · Accepted solution]

Would something like this work?

It looks for the largest year and selects only the columns that contain that year. It can be altered if needed, but its dynamic.

It uses the dynamic rename to update the field descriptions and then the dynamic select to select the fields based off those descriptions.

[alexgibson]

As of right now, it appears to look good and would work correctly. Thank you for your help on this.