We have month wise files, find the 6 or 8 or ... months data logic in script side

[Ramu_i]

Hi All,

I have data, every month one file excel file is added to folder location.

Files names are Sales_Jan_2023, Sales_Feb_2023, Sales_Mar_2023, Sales_Apr_2023, Sales_May_2023, Sales_Jun_2023, Sales_Jul_2023, Sales_Aug_2023...

I have written code is

Load * form Sales_* ;   

it will display the all month data,

But I want to need last 4 Months data, After few days clients need last 6 months data, after that required last 5 Months data .

I have created one variable 

Let vMonths=Date(AddMonths(Today(),-6),'MMM_YYYY');   --- 

This variable passing table name

Load * from Sales_$(vMonths);  but here showing only Feb data , I want to required Feb to Aug data.

In that variable If pass the value is 10 it will display the Nov -2022 to Aug-2023 data like that.

Please help on that.

Thank you

Ram

[G3S]

not sure if 'let' allows to have multiple values. 

you could try : Let vMonths >= Date(AddMonths(Today(),-6),'MMM_YYYY');  

[Ramu_i]

Hi G3S,

I tried but it is not working.

Thank you.

[Tanish]

Hi Ramu,

You can Create a variable like this 

vMinDate =    Date(Monthstart( Addmonths(Today() , -6)));

This variable will provide 6 month before starting date, for example 01/02/2023.

You can use this variable in Where condition. If you have different date format you can pass it on variable.

Load * form Sales_*

Where Date >=  vMinDate  ; 

I hope this will help....

[Ramu_i]

Hi Tanish,

That table don't have the data fields, File names have date like Sales_Jan_2023, Sales_Feb_2023...

Based on the file name will find the last 6 months. 

Last 6 means in data folders 6 excel files will load. Last 8 Months means 8 Excel files will load...

Thanks ,

Ram

[marcus_sommer]

It's not possible with your kind of file-wildcard-load. But you could switch this to a filelist-loop and the querying the wanted files within the loop. It may be something like this:

for each file in filelist('path\Sales*.xlsx')
   let f1 = replace(subfield(subfield('$(file)', '\', -1), '.', 1), 'Sales_', '');
   let f2 = date#('$(f1)', 'MMM_YYYY');
   let f3 = year($(f2)) * 12 + month($(f2));
   if $(f3) >= (year(today()) * 12 + month(today())) - $(var) then
       load ...
    end if
next 

[Ramu_i]

Hi Marcus,

I tried, but it is not working.

Thanks,

Ram

[marcus_sommer · Accepted solution]

What doesn't work? You could simply trace what happens on each step and the results will hint to what needs to be adjusted to the file- and format-pattern:

for each file in filelist('path\Sales*.xlsx')
   trace $(file);
   let f1 = replace(subfield(subfield('$(file)', '\', -1), '.', 1), 'Sales_', '');
   trace $(f1);
   let f2 = date#('$(f1)', 'MMM_YYYY');
   trace $(f2);
   let f3 = year($(f2)) * 12 + month($(f2));
   trace $(f3);
   if $(f3) >= (year(today()) * 12 + month(today())) - $(var) then
       trace $(var); // load ...
    end if
next 

By the way  $(var) is your variable offset-value which needs to be created with a valid value ...