Removing date columns based on condition

Question

Hi Community,

I have a problem with the removing of date columns based on expression.

In this case I want to remove columns not older than 3 months from date time (such as 01-02-2021).

Thanks in advance for your help.

patrick_mcauliffe · Answer

So, it's roughly the same thing, but we have to then extract the year from the existing name rather than hardcoding it.

You can get the year a couple different ways that I can think of.

Regex:

TrimRight(REGEX_Replace([Name],"\w{3}\s\(\d{2}\-",''),')')

SubString functions:

Substring(Name,8,4)

You would just use that in place of the year.

Also, I noticed in your follow up that the Name format is different than the first example you gave.

For this new format, you'd have to use this string function to get the month abbreviation for conversion:

Left([Name],3)

instead of right([Name],3)

Give it a try and post your progress if you still don't get the expected results.

MK97pro · Answer

It is a bigger problem because my data looks like this

Feb (02-2021), Jan (01-2021), Dec (01-2020), Nov (01-2020) etc.

So, when Im trying to remove the 3 newest columns (in this case Feb, Jan, Dec) i cannot do datetime parse because there are different years.

Could you try to modify it to this example?

Thank you!

patrick_mcauliffe · Answer

Can you show us what you've tried?

I'd suggest using a dynamic select tool.

Something like this (comments to explain):

NOT(
DateTimeDiff(

//I'm assuming these are all last year since that information isn't provided, however, modify this as needed
'2020-'+

//this pads the month number in the event of a single digit month
padleft(

//convert last three characters from month abbrev to month number
DatetimeMonth(
DateTimeParse(

//If more than one month in name, grab the most recent
right([Name],3)

//convert last three characters from month abbrev to month number
,'%b'))

//this pads the month number in the event of a single digit month

,2,'0')

//this sets the date part of the date format to the first of the month
+'-01'

,dateTimeToday(),'months')>3)

Also, see the attached workflow.

DynamicColumns.yxmd