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):
- 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.
- 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.
- 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.
