Hello All,
I am trying to clean up data from a system generated report. The report is ran monthly so the number of rows of data I will need will differ based on the month. I believe I will use a Multi-Row formula to create a new value and then filter out what I don't need. I just am not sure how to execute it. I have attached an example. I need the dates and amounts for each monthly report ran but there is system generated info after the data. I need to be able to select only the dates but have it dynamic so I can apply the workflow to each month. I appreciate any thoughts or suggestions.
Solved! Go to Solution.
Assuming that in the first column you want all the records that have a date (i.e. there isn't a date further down in the same column), you can use this in a Filter without using a Multi-Row Formula:
REGEX_Match([Date], '\d{1,2}/\d{1,2}/\d{4}')
See attached, I've included both this method and the Multi-Row Formula method..
Assuming the Date column is coming in as a text field then I would use a filter tool with a custom expression to filter it down to just the date:
REGEX_Match([Date], "\d{1,2}/\d{1,2}/\d{4}")
After that you should be able to process using normal formula tools.
You can then parse the string to a date using a formula tool or the datetime parse tool
Hope that gives you enough to get started
Thank you for the quick response. I got the multi row formula version of the solution to work but I think I like the filter by date version better. Unfortunately my filter by date isn't working. Would it be because the date from the report is formatted like "2017-10-19" ?
Yep. You likely can change the RegEx to:
REGEX_Match([Date], '\d{4}-\d{2}-\d{2}')
That was the issue, I appreciate your help.