creating date field using file name

[bushpalaamarnat]

could you please advise.

I am trying to load latest 6 file from a folder which is in the format as below

Temp:

LOAD *,

Date(Date#(SubField(Left(filename(),8),'YYYYMMDD'),'MM/DD/YYYY')) as Date,

filename() as FileName

FROM

(qvd);

STORE Temp into (qvd);

I am not getting the required out put.

[sunny_talwar]

Try with FileBaseName() instead of FileName() as FileName as picks up the file extension... .qvd or .xlsx

[sunny_talwar]

Read more about here

FileBaseName

[sunny_talwar · Accepted solution]

Made some other changes as well

Temp:

LOAD *,

     Date(Date#(Right(FileBaseName(), 8), 'YYYYMMDD'), 'MM/DD/YYYY')) as Date,

     FileName() as FileName

FROM

(qvd);