Read filename and derive column out of it

[PradeepKandula]

Hello,

I have data in excel file with name "Samplefile_YYYYMM.xlsx'. In my output i want to add new integer column in YYYYMMDD format where DD is the no of days in that month, for example If my filename is "SampleFile_201608" I want to add column Dt_ID as 20160831

How can I achieve this in alteryx ?

Thanks,

Pradeep

[RodL]

You can bring the filename into the data in the Input tool configuration option of "Output File Name as Field".

Once you have the field and parse out the YYYYMM characters, you could use a formula like...

DateTimeTrim(DateTimeFormat(Left([Date], 4) + '-' + Right([Date], 2) + '-01', "%Y-%m-%d"), "lastofmonth")

[PradeepKandula · Accepted solution]

Thanks RodL

It gave me 2016-08-31, I again used DateTimeFormat on what you suggested to get 20160831. Work done now i will have to understand what DateTimeTrim Function is.

DateTimeFormat(DateTimeTrim(DateTimeFormat(Left([Data_Dt], 4) + '-' + Right([Data_Dt], 2) + '-01', "%Y-%m-%d"), "lastofmonth"), "%Y%m%d")

Thanks Again,

Pradeep

[RodL]

Glad that helped.

You can think of a DateTimeTrim function as a "rounding" function...much like rounding a number to a certain level, but "rounding" a date.

So if you trim by "year", it will remove everything from the date except the year and place the date/time as of midnight on January 1st (i.e., the first second of the year).

"LastOfMonth" basically "rounds up".

Here is something from Help on that function...

• DateTimeTrim(<date/time>, <trim type>) Remove unwanted portions of a date/time and return the modified date/time.

Trim Types:

• firstofmonth:  trim to the beginning of the month (this does the same as month)
• lastofmonth: extend to one second before the end of the last day of the month
• year: trim to midnight on January 1st.
• month: trim to midnight at the first day of the month
• day: trim to the day (i.e., midnight). This will convert a DateTime to a day with a time of zero (not a date).
• hour:  trim to the hour
• minute:  trim to the minute.

[PradeepKandula]

Understood thanks.

Going back I have a question on approach it was achieved. I read the file name as column and applied formulas to get desired output into column. Here I am applying transformation to all records, Instead can I read the file name as varialble apply formulas to it and then read it for all rows in my new column?

Thanks,

Pradeep

[RodL]

Pradeep,

Sorry, but not sure I understand the follow-up question.

If you are asking if there is a different way to approach the original issue, then the answer is 'yes'. You will find there are sometimes several ways to get to the same results within Alteryx. Although typically getting the filename from within the Input Data tool is the easiest way to handle it.

[PradeepKandula]

Sorry for not being clear. Can I read the file name as varialble instead of a column. If that's possible I can just transform the variable and use it instead of transforming all rows in the new column

I am not sure If I conveyed it better this time 

Thanks,

Pradeep

[RodL]

Thanks for the clarification.

You will find there's always different ways to do things within Alteryx, although purely reading in a file name as a variable (assuming you want a single record) is not something that is available natively.

For example, you could use a Directory tool to get the list of file names from a directory, filter on the one you want, and send that through a Dynamic Input tool to get your data. You could then tranform what you want from the file name and Append that to each column as it comes out of the Dynamic Input tool.

That said, just selecting in a regular Input tool that you want the file name and attaching to a Formula to transform is much more intuitive to me. And you will find with Alteryx that "transforming all rows in the new column" has no effect on performance.

Hope this helps.