second last max date

[capriconuser]

Hi my script is 

There is multiple files in folder like this 

Abc-Jan-2021.xlsx
Abc-Feb-2021.xlsx
Abc-Mar-2021.xlsx

now I successfully extract Month , Year and Date from these file name like this 

Table1:
LOAD DisplayName as FullName, 
      EmailID,
     Trim(Replace(FileBaseName(),'Abc-','')) as Mail_Date
FROM
[data\Usage\Abc-*.xlsx]
(ooxml, embedded labels, table is Sheet1);

Date_Table:
Load *,
MonthEnd(MakeDate(Right(File_Month, 4), Num(Month(Date#(Left(File_Month, 3), 'MMM'))))) as Max_Date
Resident Table1;

now here i extract Max_Date from files.. and then i store in varaible like this 

Let vMaxDate = Num(Floor(Peek('Max_Date', 0, 'Date_Table')));
Let vSecondMaxDate = Num(Floor(Peek('Max_Date', -1, 'Date_Table')));

now i want to extract second last max date.. i.e. in these files 

max date is 

31-03-2021
and second last max date is 
28-02-2021

so how i get second last max date?

[MayilVahanan]

HI

It will give the second max date

Load Max(Max_Date,2) as SecondMaxDate Resident Table1;

[Kushal_Chawda]

@capriconuser   after creating date, perform below load.

max:
LOAD date(max(Date)) as max_date, date(max(Date,2)) as second_max_date
Load fieldvalue('Max_Date', recno()) as Date
autogenerate fieldvaluecount('Max_Date');

Now you can use below below variables

Let vMaxDate = floor(Peek('max_date', 0, 'max'));
Let vSecondMaxDate = Floor(Peek('second_max_date', 0, 'max'));