topic Re: How to create Master calendar from Excel file name? in QlikView

How to create Master calendar from Excel file name?

silambarasan — Tue, 12 Apr 2016 15:00:31 GMT

Example File name:

First File : Excel_File_2016-03-29.CSV

Second File :Excel_File_2016-04-04.CSV

tresesco‌Scripting‌QlikView Forums & Resources‌

Re: How to create Master calendar from Excel file name?

marcus_sommer — Tue, 12 Apr 2016 15:03:06 GMT

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

- Marcus

Re: How to create Master calendar from Excel file name?

settu_periasamy — Tue, 12 Apr 2016 15:10:54 GMT

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

Re: How to create Master calendar from Excel file name?

brunobertels — Tue, 12 Apr 2016 15:28:12 GMT

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

Re: How to create Master calendar from Excel file name?

Kushal_Chawda — Tue, 12 Apr 2016 15:30:52 GMT

// 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)';