Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Scripting Issue

Hi All,

I have a list of filenames such as YYYY.MM Abc.xls, Xyz MM_YYYY.xlsx and many more.

I want the following things o be separated from the filename:

1. Date Format i.e. YYYY.MM for the first file, MM_YYYY for the second and so on.

2. Filename excluding the date format i.e. Abc for the first file and Xyz for the second one.

Is it possible? If yes, then how?

Thanks,

Asma

8 Replies
anbu1984
Honored Contributor III

Re: Scripting Issue

Load *,KeepChar(FileBaseName(),'0123456789._') As Date,PurgeChar(FileBaseName(),'0123456789._') As FileNm

From Excel.xlsx

Re: Scripting Issue

You could use string-functions like left/mid/right to extract date-informations from filebasename() and/or removed all unwanted chars with keepchar, see here various possibilities:

table:

Load

     *,

     left(filebasename(), 7) as [YYYY.MM],

     makedate(left(filebasename(), 4), mid(filebasename(6, 2), 1) as Date,

     keepchar(filebasename(), '0123456789_') as [MM_YYYY]

From x;

- Marcus

Not applicable

Re: Scripting Issue

Thanks for the quick reply!!

File names are getting separated properly.

Bu the files with filenames as MM.YYYY YTD_ABC Test_XY.xls, is storing MM.YYYY_ in Date Format and YTDABC TestXY in filename.

Also, some files are in the format ABC December 2014.xls. So in this case, the file name should be just ABC and File Date Format should be December 2014.

anbu1984
Honored Contributor III

Re: Scripting Issue

Do you have list of formats in which you will get filenames?

Not applicable

Re: Scripting Issue

Yes. The list is maintained in a separate column in the excel. The column has values as ABC MMMM YYYY.xls for a file and the corresponding filename is ABC December 2014.

Every month just the month name section of the filename would change.

anbu1984
Honored Contributor III

Re: Scripting Issue

For the files in format "ABC MMMM YYYY.xls", you can use this

SubField(FileBaseName(),' ',1) As FileNm

TextBetween(FileBaseName() & '$$',' ', '$$') As Date

Not applicable

Re: Scripting Issue

Does this mean that I have to apply different conditions based on the filename?

Can't we just write a single expression which takes care of separating the file name and date format part irrespective of the file name.

Thanks,

Asma

anbu1984
Honored Contributor III

Re: Scripting Issue

Yes you should use different conditions based on filename

Community Browser