topic Re: Scripting Issue in QlikView

Scripting Issue

Mon, 11 May 2015 09:20:56 GMT

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

Re: Scripting Issue

anbu1984 — Mon, 11 May 2015 09:28:34 GMT

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

From Excel.xlsx

Re: Scripting Issue

marcus_sommer — Mon, 11 May 2015 09:34:56 GMT

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

Re: Scripting Issue

Mon, 11 May 2015 09:38:55 GMT

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.

Re: Scripting Issue

anbu1984 — Mon, 11 May 2015 10:07:46 GMT

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

Re: Scripting Issue

Mon, 11 May 2015 10:13:25 GMT

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.

Re: Scripting Issue

anbu1984 — Mon, 11 May 2015 10:46:29 GMT

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

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

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

Re: Scripting Issue

Mon, 11 May 2015 11:54:33 GMT

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

Re: Scripting Issue

anbu1984 — Mon, 11 May 2015 12:36:21 GMT

Yes you should use different conditions based on filename