Identify latest date within the field and rename

[ck2019]

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

[afv2688]

Hi @ck2019!

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

[ck2019]

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.

[afv2688]

Well you can sort after using the sort tool on the date colum just created in descending order.

I don't know if this is what you are looking for though.

[afv2688]

Maybe what you are looking for it the dynamic rename tool, where configuring an expression you can rename the fields as wanted.

You can see it on the example given by the tool

[ck2019]

Hey,

Yes thanks with your help I have got this far so far with the dynamic rename tool

if contains([_CurrentField_],"Outstanding Options at") Then 
right([_CurrentField_],11) else [_CurrentField_] Endif

This gives the column name as the date.  What I want is to identify the column name with the latest date and then rename it with "No of Shares".  I thought some form of transpose, sort and then append back to the original data might be a possible solution, but I am struggling to move on to the next step...

[neilgallen · Accepted solution]

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

[ck2019]

This is exactly what I was thinking but I would have got no where near :-)

Thank you - I clearly still have a lot of learning to do.