DateTime Transformation, Conversion to Week Number, and conditional MID extraction

[eh7spartan]

Hello,

I'm having trouble with a two-part join of two tables. This join requires some field transformation and column creation in both source tables--this is where I'm struggling. Tables below and attached. Any and all help is much appreciated! Thanks very much for your consideration.

Table 1 format:

ID Number	test date	student
ID 1016-STNT	5/26/2020 7:59	James
ID 0630-STNT	5/28/2020 15:09	Alyssa

Table 2 format:

ID Number	date test was graded	Grade
1016	5/30/2020 0:00	A
630	5/31/2020 0:00	B

What I'm trying to do (list of needs):

1. Transform the two ID number fields into the same format so that they can be used for a join--matching the grade to each student.
2. Transform date fields (currently string) into datetime format so that the difference (in days) between the dates can be calculated. Want to know how many days elapsed between date of test and date test was graded.
3. Transform both date fields into calculated fields which display the week number of the year (1-53), so that I can look for trends across particular weeks of the year and aggregate results by week.--Per below, I believe my current formula for this will work once step 1 above is complete.

Current steps I've tried:

Was trying to use the MID formula to extract ID number from table 1, but what I'm finding is I also need a way to conditionally remove the leading 0 from the 3 digit IDs in order to match all IDs successfully to table 2. The mid formula only allows me to match all 4 digit IDs OR all 3 digit IDs.

Additionally, I'm trying to use a datetime convert step in order to turn both date fields (string) into a version that Alteryx can understand and subtract using (DateTimeDiff([date test was graded],[test date],'days')). Even selecting a "custom" format description for the incoming string, Alteryx doesn't seem to understand the incoming date data based on my description and therefore it is not converting it succesfully. 

For the week_of_year column creation, it looks like the datetimeformat([test date],"%W") should work once the dates are converted into datetime format with the convert step… So I may not need additional help with this step once step 1 above is successful. 

Advice on any and all of these three items would be a wonderful help. Thank you.

[eh7spartan]

Here's the community article I referenced for the datetime convert step I tried above: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-the-week-number/td-p/12234

Here's the community article I referenced for the weekofyear formula step I tried above: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-the-week-number/td-p/12234

[ncrlelia]

Hi @eh7spartan,

1. After applying the substring formula, you an use a select tool and change the data type to double/integer, this will remove the 0 in front as now they are in numeric format.
2. You can use MM/dd/yyyy hh:mm

Attached workflow for your reference. Hope this helps.

Thank you.

Best Regards,

Lelia

[binu_acs]

@eh7spartan One way of doing this

[eh7spartan]

Thank you very much for your help!

[Christina_H]

I went a slightly different way with this, converting the ID numbers to strings rather than trying to extract the number from the string:
'ID '+padleft(ToString([ID Number]),4,'0')+'-STNT'

Then converting the dates with this formula:

DateTimeParse([test date],'%m/%d/%y %H:%M')

(the time part is probably unnecessary!)

Then you should have no problems with the difference and week numbers.