Creating Different Formats for the Date Field

[StockMarket]

Hello

I have to create a BATCH MACRO in which I have to process multiple csv files. In this workflow, I am working with csv files and the very first field present inside my data by the name of "filename" is actually a DATE VALUE in this format - YYYYMMDD.

I want to make use of the date value present within that field, for creating these separate new fields inside my data -

Field name "dd" -
which will have the DATE Values from 01,02 to 31, containing the LEADING ZERO as well, wherever applicable.

Field name "mmm" -
which will have the MONTH Value in the form of first 3 alphabets like JAN, JUL, DEC etc.

Field name "mmm_2" -
which will have the MONTH Values from 01,02 to 12, containing the LEADING ZERO as well, wherever applicable.

Field name "yy" -
which will have the YEAR Values in form of LAST 2 digits like 01,02 etc. containing the LEADING ZERO as well, wherever applicable.

Field name "yyyy" -
which will have the YEAR Values in form of total 4 digits like 2018,2019 etc.

And finally in the very last step, I have to create 2 new DATE FIELDS which will display the combined Date Value in this format -

Field name "new_date_1" -
which will have the actual date in this format, along with the 2 dash "-" in between, as shown below -
DD-MMM-YYYY
for example - "28-Jan-2021"

Field name "new_date_2" -
which will have the actual date in this format -
DDMMMYY
for example - "28JAN21"

So basically these 7 NEW FIELDS have to be created, based on the logic explained above.
And all of these new fields will make use of the very first field named "filename" as a source for their data.

"dd"
"mmm"
"mmm_2"
"yy"
"yyyy"
"new_date_1"
"new_date_2"

And also please suggest, which particular tool should be used for COMBINING two or more fields together ex. if I want to create a new field by the name of "ticker_mmm" which will be combination of "ticker" and "mmm" field "which is just created above", then which tool to use for doing this?

If I have to create a field which is a combination of three of four different fields, then can I do that in one single step itself?

I have also attached the Alteryx Worflow Package .yxzp file and the sample data file, so that you can easily open it up on your machine. I have used the latest version of Alteryx for creating this workflow. You may please have a look.

Thanks and Regards

 

 

 

[danilang · Accepted solution]

Hi @StockMarket 

Here's another version of your macro, that adds the various data fields

Here I've add another formula tool that uses DateTimeFormat() to extract the various components.  I've only added 3 of your fields, but you should be able to use the format information here to add the remaining parts 

The final formula (Ticker_mmm) shows you how to combine the values from 2 fields into a single one.

Dan

[StockMarket]

Thank you so much @danilang 

It works perfectly for me. I created the other fields, using the link that you provided.

Your solutions are always so efficient. I am really grateful.

by any chance, could you please take a shot at this question as well -

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Matching-the-Field-Names-Properly/m-p/...

because the solution provided looks fairly complicated to me and I am wondering that there should be an easier approach for comparing the data fields, if they are named properly and are in correct sequence or not. Can you please suggest some alternative approach to do the same?

Thanks and Best Regards