topic Re: second last max date in Integration, Extension & APIs

second last max date

capriconuser — Tue, 10 Aug 2021 08:41:22 GMT

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?

Re: second last max date

MayilVahanan — Tue, 10 Aug 2021 08:58:29 GMT

It will give the second max date

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

Re: second last max date

Kushal_Chawda — Tue, 10 Aug 2021 22:15:43 GMT

@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'));