Identify latest date within the field and rename

ck2024

Hi everyone

I've been introduced into Alteryx in the last couple of weeks, and I love it! Previously worked with macros in Excel (by no means an expert but enough to be sufficient) but primarily by using formulae to achieve what I wanted. I'm getting to grips with the basics of Alteryx but do struggle with Regex expression and I think this is what I need to achieve this

I have one scenario which I'd appreciate some input on.

Basically I have a file that has fields with various dates in but the one I am interested in is the one with the latest date e.g.

Outstanding Options at 01-Mar-2018

Outstanding Options at 28-Feb-2019

There might be other fields with 28-Feb-2019 within it but I only really need to worry about the one with Outstanding Options within it.

What I want to do is identify the second column and rename it to something like No. of Shares irrespective of when the file is generated.

I have a feeling the dynamic rename with some form of contains and regex formula would do it, but not sure how to enact it...some guidance would be very much appreciated.

I thought about

if [field] contains [Outstanding Shares] then Datetimeparse(Substring([field],23,11),"%d-%b-%y")

would get be to be able to extract the date but not then figure out how to only rename the one with the latest date, or at least only to rename that field.

Thank you in advance.

Cass

Regex

Parse

Accepted answers

neilgallen

@ck2024 perhaps a bit more convoluted than it needs to be, but it's early in the morning so....

It's a unique problem because you're working with column headers, not the data itself. So we need to approach this in a slightly different way. The savior here is the "Field Info" tool, which gives us information about each of our fields, including the header name. From there it's a bit of manipulation to identify the fields in question and then rename the appropriate one. Unioning this back to our original data (configured by position, not name) then lets us use a dyanamic rename tool to get the column headers back.

identify latest date within field and rename.png

identify latest date within field and rename.yxmd

All comments

afv2688

Hi @ck2024!

Use the formula tool first: < Right([Field1], 11) >

Then, on the new field, use the DateTime tool on custom with the following string: < dd-Month-yyyy>

Regards

ck2024

Thanks for that. I think I understand how that works on formula basis to manipulate the data, but what I really need to understand is how to build that into a dynamic rename so that it only searches out the column with that description but in particular the one that has the latest date. Is that viable?

Thanks

Information in this e-mail and any attachments is confidential, and may not be copied or used by anyone other than the addressee, nor disclosed to any third party without our permission. There is no intention to create any legally binding contract or other binding commitment through the use of this electronic communication unless it is issued in accordance with the Experian Limited standard terms and conditions of purchase or other express written agreement between Experian Limited and the recipient. Although Experian has taken reasonable steps to ensure that this communication and any attachments are free from computer viruses, you are advised to take your own steps to ensure that they are actually virus free.

Experian Ltd is authorised and regulated by the Financial Conduct Authority.
Companies Act information: Registered name: Experian Limited. Registered office: The Sir John Peace Building, Experian Way, NG2 Business Park, Nottingham, NG80 1ZZ, United Kingdom. Place of registration: England and Wales. Registered number: 653331.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703