How to Store Previous date of max date in a variable?

[#1]

Hi All,

I have a Data like below table

TGB Name	Date	TGB_Counts
Bala_AMI	04/06/2017 PM	4
Bala_AMI	04/07/2017 PM	5
Bala_AMI	04/08/2017 PM	9
Bala_AMI	04/09/2017 PM	2
Metro_AMI	04/06/2017 PM	3
Metro_AMI	04/07/2017 PM	6
Metro_AMI	04/08/2017 PM	8
Metro_AMI	04/09/2017 PM	12
Cheta_AMI	04/06/2017 PM	7
Cheta_AMI	04/07/2017 PM	15
Cheta_AMI	04/08/2017 PM	13
Cheta_AMI	04/09/2017 PM	2

from above table, i want to store following dates in a variable

Current day: 04/09/2017 PM

Yesterday: 04/08/2017 PM

Day before yesterday: 04/08/2017 PM

I have stored the current date in a  variable by using below script logic

Script logic:

RollMax_date:

LOAD MaxString(Date) as RollNewDate

Resident Sheet1;

Let VRollCurrentday=Peek('RollNewDate',0,'RollMax_Date');

Trace $(VRollCurrentday);

Drop table RollMax_date;

But I cant able to store Yesterdays date(04/08/2017 PM) and Day before yesterday date (04/08/2017 PM) in a variable since the date is in string format

I want to store Yesterdays date(04/08/2017 PM) and Day before yesterday date (04/08/2017 PM) in a variable

Please find the attached Working file and Dat Excel and S hep me out of it

Thanks,

Muthu

[sunny_talwar]

May I ask what does PM in your date field stands for?

[#3]

Its like AM and PM for a day

we are taking only PM data

[micheledenardi · Accepted solution]

Sheet1:

LOAD [TGB Name],

     Date,

     TGB_Counts,

     D,

     E,

     F,

     G,

     H,

     I,

     J

FROM [Pos.xlsx] (ooxml, embedded labels, table is Sheet1);

RollMax_date:

LOAD

  MaxString(Date) as RollNewDate

Resident Sheet1;

Let VRollCurrentday=Peek('RollNewDate',0,'RollMax_Date');

Trace $(VRollCurrentday);

Drop table RollMax_date;

RollYesterday_date:

LOAD

  MaxString(Date) as RollYesterdayDate

Resident Sheet1

  where Date<> '$(VRollCurrentday)';

Let VRollYesterday=Peek('RollYesterdayDate',0,'RollYesterday_date');

Trace $(VRollYesterday);

Drop table RollYesterday_date;

RollBeforeYesterday_date:

LOAD

  MaxString(Date) as RollBeforeYesterdayDate

Resident Sheet1

  where Date<> '$(VRollCurrentday)' and Date <> '$(VRollYesterday)';

Let VRollBeforeYesterday=Peek('RollBeforeYesterdayDate',0,'RollBeforeYesterday_date');

Trace $(VRollBeforeYesterday);

Drop table RollBeforeYesterday_date;

If you want to remove PM use Replace funcion.