Date issue

[bobbydave]

XXXXXXXXX_MSL v0.169 Apr16.xlsx

XXXXXXXXX_MSL v0.169 May16.xlsx

XXXXXXXXX_MSL v0.169 June16.xlsx

XXXXXXXXX_MSL v0.169 July16.xlsx

XXXXXXXXX_MSL v0.169 Aug16.xlsx

I want to get the date from this file.

(Subfield(SubField(filename(), ' ', 3), '.', 1)  as Date

Result:

Apr16

May16

June16

July16

Aug16

Which is fine until I change it into the date format.

Date below converted wont pick up July and June as they are in MMMM and not MMM format like Apr May and Aug

Date(Date#(Subfield(SubField(filename(), ' ', 3), '.', 1), 'MMMYY'), 'MMM YYYY')  as Date

Apr 2016

May 2016

Aug 2016

Any ideas how I can get all months?

[sunny_talwar]

How about this:

Date(MonthStart(Alt(Num(Date#(Left(SubField(FileBaseName(), ' ', -1), 'MMMYY')), Num(Date#(Left(SubField(FileBaseName(), ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date

Date(MonthStart(Alt(Num(Date#(SubField(FileBaseName(), ' ', -1), 'MMMYY')), Num(Date#(SubField(FileBaseName(), ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date

[sunny_talwar · Accepted solution]

Here is a sample for you to play around with

LOAD *,

  Date(MonthStart(Alt(Num(Date#(SubField(FileBaseName, ' ', -1), 'MMMYY')), Num(Date#(SubField(FileBaseName, ' ', -1), 'MMMMYY')))), 'MMM YYYY') as Date;

LOAD * Inline [

FileBaseName

XXXXXXXXX_MSL v0.169 Apr16

XXXXXXXXX_MSL v0.169 May16

XXXXXXXXX_MSL v0.169 June16

XXXXXXXXX_MSL v0.169 July16

XXXXXXXXX_MSL v0.169 Aug16

];

[sunny_talwar]

In case you want to stick with FileName() function, rather than FileBaseName(), you can try this:

Date(MonthStart(Alt(Num(Date#(SubField(SubField(FileName(), ' ', -1), '.', 1), 'MMMYY')),

  Num(Date#(SubField(SubField(FileName(), ' ', -1), '.', 1), 'MMMMYY')))), 'MMM YYYY') as Date

Sample:

LOAD *,

  Date(MonthStart(Alt(Num(Date#(SubField(SubField(FileName, ' ', -1), '.', 1), 'MMMYY')),

  Num(Date#(SubField(SubField(FileName, ' ', -1), '.', 1), 'MMMMYY')))), 'MMM YYYY') as Date;

LOAD * Inline [

FileName

XXXXXXXXX_MSL v0.169 Apr16.xlsx

XXXXXXXXX_MSL v0.169 May16.xlsx

XXXXXXXXX_MSL v0.169 June16.xlsx

XXXXXXXXX_MSL v0.169 July16.xlsx

XXXXXXXXX_MSL v0.169 Aug16.xlsx

];