Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
You have each file starts with this name Value by Sheet Aug'17 - TSD.xlsx then use the filebasename() with combination.
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);
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?
Yes it is dynamically changed according.