Convert Date/Time Field Format to a Sequence ID

Question

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.

KaneG · Accepted Answer

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

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

Kane

MSalvage · Accepted Answer

@RifferX,

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