2022.1.1.30569 Patch Release Update

The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs. If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.
alteryx Community

# Alteryx Designer Discussions

SOLVED

## Convert Date/Time Field Format to a Sequence ID

8 - Asteroid

For pHI purposes, need to convert a Procdate column into a large number based SequenceID.

Date/time format: 2014-04-06 02:02:28.000

Desired Output: 12,439,369 (something similar -- basically to trim the date/time down to a day with each day having its own SequenceID)

In excel, I used different formula combos in separate steps. I am not sure what approach to take with Alteryx and was looking for some ideas. There are going to be a few million rows by calendarweek so in essence each time I run this process there will only be 7 new SequenceIDs since I am creating a new, aggregated table on a weekly basis.

4 REPLIES 4
Alteryx

You can just take the number of days from an arbitrary point...

DateTimediff(Left([Date],10),'2000-01-01','days')

Kane

11 - Bolide

Not totally sure if this gets at exactly what you want, but it might help you down the right path(attached).

Best,

MSalvage

EDIT: The actual math in my sample workflow will not necessarily be a unique ID, but might help you use your formulas.

Ex: (2014yr)*365 + (01m)*30 + (31d) = 735171

and (2014yr)*365 + (02m)*30 + (01d) = 735171

8 - Asteroid

Thank you sir. (KaneG) Had to jump on at home to try it out and it took a couple hours to run. Works well although I may have to change the default date and add some extra steps to provide more security. Appreciate your time and seems like a great solution.

8 - Asteroid

MSalvage, this is a viable solution and provides a different angle of attack than KaneG's. Some extra steps but nothing that would be a deterrent. Definitely appreciate your time and effort in helping me out and this will also provide a nice little workflow reference. Have a great Thanksgiving!

Labels