Hi,
I am pretty new in regular expression and been having trouble understanding it. I want to parse the date from a file name.
For example:
File name : FFTT INC Created_2023_02_01
Date format in filename: yyyy_mm_dd
I want to extract the date so I can sort the files by date in alteryx for dynamic input. I have tried sorting with creation date but I have some reports which have been created on same dates but the data is for another date. Hence this request.
Any help is greatly appreciated. Thanks
Nithin
Solved! Go to Solution.
Hey @Nithinpanicker, would something like this work for you? Don't think there's any need for RegEx unless I'm missing the purpose here. The innermost Right() expression isolates the last 10 characters of the filename (your date). Then, we just convert this into an ISO date that Alteryx can work with (YYYY-MM-DD format) using DateTimeParse() and defining the incoming YYYY_MM_DD format:
One way of doing this:
DateTimeParse(REGEX_Replace([File Name], '(.*)(\d{4}_\d{2}_\d{2})(.*)', '$2'), '%Y_%m_%d')
Realised after posting initially that we don't even need to mess around with date functions etc as we're literally just going from YYYY_MM_DD to YYYY-MM-DD and so can simplify this even further with a formula along these lines. The output date type being set to a date will allow you to sort properly:
Hi @DataNath @Felipe_Ribeir0 @ShankerV
Thank you all for your valuable replies.
Yes all the proposed solutions are giving me the desired outcome.