Date range in a dynamic path

[MEBG93]

Hello everyone!

I need to load the last 6 months of data stored in monthly files, as if, 'file_YearMonth'.

So I need a dynamic path that only retrieves the last 6 months from the maximum date within the Current Month file. This file is named differently as if 'file'.

I've been trying to make a where clause inside the from name, but can't seem to figure it out. I've also tried the asterisk (*) to call every file named with the '_YearName' suffix, but seems useless after the next month.

Cheers,

MB

[maasool]

Create variable(s) for the paths of the files and update them in the beginning of the new month:

let PATH1 ='lib://P/Files/file_201910'
let PATH2 ='lib://P/Files/file_201909'
...
let PATH6 ='lib://P/Files/file_201905'

Created variables you can use in data load:

LOAD
*
FROM [$(PATH1).xlsx]

[MEBG93]

Thanks Maasool, but I need to update the script automatically without manually editing.

[maasool]

Then you should create additional variables for file names, which change with dates.

Example for November file, with name 'nov2019':

let PATH1='lib://P/';

let MONTH1=Month(today(1));
let YEAR1=year(today(1));

Testdata:
LOAD
Test
FROM [$(PATH1)$(MONTH1)$(YEAR1).xlsx]
(ooxml, embedded labels, table is Sheet1);

[annacastan]

Hi, 

I had the same problem and I have solved like this:

// FILENAME_YYYYMM.qvd from 201701 until today 202109
FOR EACH vFile IN FILELIST ('[..\..\FILENAME_*.qvd]');
        // First extract only the year of the file
	LET vYear = left(right('$(vFile)', 10), 4); 
	TRACE ---- $(vYear);
	// Only want from 2 years back until today data
	LET vLimitYear = date(YearStart(Addyears(today(),-2)), 'YYYY');
	// If the year of the file is equal or bigger than this two years back until today... then load the table
	IF $(vYear) >= '$(vLimitYear)'
	        // here you can write the load table or whatever you want to do. In this case is only a trace.
		TRACE $(vFile);
		
	ENDIF
	
NEXT

I know that your post was from 2019, but maybe for another person that have the same problem. 

BR,

Anna