Tool Mastery | Multi-Row Formula

[Jbelluardo]

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. 

 

[danrh]

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..

[jdunkerley79]

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

[Jbelluardo]

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" ?

[danrh · Accepted solution]

Yep. You likely can change the RegEx to:

REGEX_Match([Date], '\d{4}-\d{2}-\d{2}')

[Jbelluardo]

That was the issue, I appreciate your help.