Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Example File name:
First File : Excel_File_2016-03-29.CSV
Second File :Excel_File_2016-04-04.CSV
Have a look here: How to use - Master-Calendar and Date-Values.
- Marcus
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
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
// 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)';