We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Batch code to datetime

winnie12
7 - Meteor

I've production batch code and would to convert to date time. 

 

EX  Batch code =180212 

 

18 mean Year 2018

02 mean week 02 of Year 2018

1  mean monday of week 2 on Year 2018

2 mean shift time

 

180212 is 08/01/2018 

 

How could i do it?

9 REPLIES 9
MichalM
Alteryx
Alteryx

Hi @winnie12

 

Would the attached work?

 

Here's what I've done

 

  1. Turn the Batchcode into a string so that I can use string function to separate the individual elements from it
  2. Drop the shift time detail as it's not needed
  3. Use a regular expression to split the remaining information into three groups - year, week and day numbers
  4. I then use DateTimeParse to turn the short year to 1st of January that year
  5. Using the formula tool I calculate

A. First day of Week 1 of the year from the batch code

DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days")

 

B Using A, we can calculate the first day of the week from the batch code

DateTimeAdd([FirstDayOfWeek1], (ToNumber([WeekNo]) - 1) * 7, "days")

 

C Using B, we can calculate the date - adding number of days -1 to the first day of the week

DateTimeFormat(DateTimeAdd([FirstDayOfWeekNo],ToNumber([DayNo])-1,"days"),"%Y-%m-%d")

Hope that makes sense.

 

Michal

 

 

 

 

winnie12
7 - Meteor

I can't open you file due to my company don't have upgrade program. I still use license version 11.5 could you help to save as lower version or capture your workflow as picture?

MichalM
Alteryx
Alteryx

Of course! See the attached.

winnie12
7 - Meteor

 

 

winnie12
7 - Meteor

May be because of week count of ours company difference from std. I've attached calendar Y16 to you in attached.

MichalM
Alteryx
Alteryx

That's what I suspected. Is it safe to assume that Week 1 is defined as a week days of which all fall into the same year and not the week of 1st of January?

winnie12
7 - Meteor

Mostly week 1 should be first week of the year. So, I can use your formulation and do some adjust if the first week not fall into the same year. thank you very much. You can help me a lot. :-)

MichalM
Alteryx
Alteryx

Exactly! What I'd probably do is update the FirstDayOfWeek1 formula and build it out as a conditional 

 

If DateTimeFormat([FirstOfYear], "%Y") = DateTimeFormat(DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days"), "%Y")
THEN DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7), "days")
ELSE DateTimeAdd([FirstOfYear],Mod(1-ToNumber(DateTimeFormat([FirstOfYear],"%w")), 7) + 7, "days")
ENDIF

We'd be checking whether the initially generated date has the same year value as FirstOfYear (1st of Jan). If this is the case, all's good. If not we'd be adding 7 more days to the expression - highlighted in red above.

winnie12
7 - Meteor

Thank you very much.

Labels