Making static column dynamic and mapping the column again to its data

[elicedas]

Hi I have column names from my input files as jan-23, feb-23, march-23
My condition is when the workflow will run on October-23 the it should pick up data from Sep-23 which is a static column . ( It will go on for the month it will be run)

How can I handle when year changes and the column name will also change like jan-24, feb-24

[sforesti]

Hi @elicedas,

If I’m following, you want to pick up the column from your file that corresponds to the last calendar month without having to manually filter columns. You can accomplish this by getting today’s date, shifting backwards by a month, converting the date to a string with the MMM-yy format, and then using that that date to filter in a Reference Column Resorter node.

Hopefully this screenshot illustrates how you might do that.

[elicedas]

Hello @sforesti , I tried your method but in reference column resorted i m getting all the columns

So my thing is when i would write an logic in column expression
I have to write like
if variable(date_input)=“Jan-24”
{
Column(Dec-23) #here it would map the data in Column(dec-23) in output column
}

but if next time it would be Jan-25 the column(Dec-24) is required but it would be Column(dec-23), it would throw an error right ? How can i resolve it

[iCFO]

I typically use Regex Rename to modify targeted column names to something static, then after processing I use Column Renamer to return it to the original column name via flow variable.

You can also reference columns via position instead of name if that works for your structure.

[elicedas]

I tried @iCFO but it didnt work . if you could provide a snapshot

[sforesti]

Hi @elicedas,

Did you configure the Reference Column Resorter node to drop unspecified?

[iCFO]

I re-read your first post. I think that you want to select the prior month from the current month of run time, then rename that column to something static for formula use, then return it to the original column name. I would need a workflow to try and tackle this as it will require a few steps and column name variations could require different method approaches. Here is something to get you started.

You can get the current date info at execution time using this approach.

Add Current Date

Hi @SergioSB , current date and time is something I’d almost expect there to be a dedicated node for, but strangely it’s a little hidden in less obvious nodes. Here are a couple of options You can use Date&Time Configuration [image] [image] Note that I added a flow into Date&Time Configuration to ensure that it is reset and re-executed if the workflow is re-run to ensure current execution date. Set the return type to Date, and tell it to return execution time. Variable to Table Column can …

Then Date & Time Shift set to Month and -1

Then Date & Time Extract to get the month and year in a separate column.

Then Column Expressions to make the month and name formats match your column names / combine them so they match your column name format.

Then convert that to a flow variable to and use it with the Column Renamer to switch to a generic name

Then use the same flow variable to return to the original name using a Column Renamer after your formulas.

[elicedas]

I made the changes , it solved my issue . thank you for the solution

[sforesti]

Glad to hear it worked out!