How do you make a date from a file name e.g. [Value by Sheet Aug'17 - TSD.xlsx] need to make a field [Link_Date] with value as 01/08/2017?

[nickjose7]

How do you make a date from a file name e.g. [Value by Sheet Aug'17 - TSD.xlsx] need to make a field [Link_Date] with value as 01/08/2017?

[its_anandrjs]

You have each file starts with this name Value by Sheet Aug'17 - TSD.xlsx then use the filebasename() with combination.

[its_anandrjs · Accepted solution]

Do this with combination

MonthStart( Date( Date#(PurgeChar( SubField( FileBaseName(),' ',4),Chr(39)),'MMMYY'),'MMMYY')) as

[Link_Date]

LOAD *

MonthStart( Date( Date#(PurgeChar( SubField( FileBaseName(),' ',4),Chr(39)),'MMMYY'),'MMMYY')) as [Link_Date]

FROM

[Value by Sheet Aug'17 - TSD.xlsx]

(ooxml, embedded labels, table is Sheet1);

[nickjose7]

Thanks Anand. This worked well.

If next month this file name is replaced by [Value by Sheet Sep'17 - TSD.xlsx] will it change dynamically?

[its_anandrjs]

Yes it is dynamically changed according.