How to create Master calendar from Excel file name?

[silambarasan]

Example File name:

First File :  Excel_File_2016-03-29.CSV

Second File :Excel_File_2016-04-04.CSV

tresesco‌Scripting‌QlikView Forums & Resources‌

[marcus_sommer]

Have a look here: How to use - Master-Calendar and Date-Values.

- Marcus

[settu_periasamy]

Can you give more detail? Do you want to create the Master Calendar?

Or Already you have the calendar in excel - Just give the Excel name as Table name.  ??

If so, you can just use FileName() , when you loading your data.

or , May be you are looking like this

Right(Replace(FileName(),'.CSV',''),10) as Date

[brunobertels]

Hi

use the filename() function in you script

as the name of your file is like this "Excel_File_YYYY-MM-DD" , you need to get only this part YYYY-MM-DD

so use subfield() function to get the date part

then make date of this string using date or date# function

not sure all those function can be concatenate in the same expression :

something like this may be

Load

Dim1,

Dim2,

etc,

filename() as name_field,

makedate(subfield (name_field,'_',3),'YYYY-MM-DD')) as date

FROM [lib://yourpath/Excel_File_****-**-**.csv]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

hope it helps

bruno

[Kushal_Chawda]

// Give folder path where all csv files are there

For each vFileName in FileList('E:\QlikView\RND\Test\*.csv')

Data:
LOAD FileName,
date(SubField(SubField(SubField(FileName,'\',-1),'_',-1),'.',1)) as Date;
LOAD SubField('$(vFileName)','\',-1) as FileName
AutoGenerate 1 ;

Next vFileName

MinMaxDate:
LOAD min(Date) as MinDate,
max(Date) as MaxDate
Resident Data;

let vMaxDate = Peek('MaxDate',0,'MinMaxDate');
let vMinDate = Peek('MinDate',0,'MinMaxDate');

Calendar:
LOAD *,
Year(Date) as Year,
month(Date) as Month,
date(MonthStart(Date),'MMM-YYYY') as MonthYear;
LOAD date('$(vMinDate)'+IterNo()-1) as Date
AutoGenerate 1
while  '$(vMinDate)'+IterNo()-1 <= '$(vMaxDate)';