Max Date Same Field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a file which has concatenated multiple dates into one field separated by a comma. Is there a way I can reference the max date within the field in a new column?
Thanks in advance
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here's one approach with some fake data, essentially use the text to columns tool to split out the concatenated dates into rows, and then summarize by max date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The following works if the dates (within a single record) are always in chronological order (ie. max date on the far right):
Otherwise, you can include a RecordID, clear spaces from the concatenations, text-to-rows on the comma delimiter, and finally, summarize by RecordID to get the MAX(Date):
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for the help. I've followed your flow below but it's coming back with mixed results - I think it might be because the date field is a string and it isn't able to distinguish the max/min date. I've tried to change the type to date but it didn't like it and brought back null values
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks! This has worked (I was trying to use existing column for Summarize output - new column brought back correct dates and format).
Thanks for the help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Your post has the same use case as I do, except that my date format is different and I require minimum. I was encountering an error where the rows with 1 date seem to be going through as Date format, while the ones with multiple dates are String that when the DateTime tool is used, the ones with 1 date is not converted properly. Please see here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Min-Date-Same-Field-Comma-Sepa...
Wondering if anyone here could help please? Thank you!