Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
HI
It will give the second max date
Load Max(Max_Date,2) as SecondMaxDate Resident Table1;
@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'));