topic Re: How to Store Previous date of max date in a variable? in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Thu, 01 Jun 2017 14:36:11 GMT

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

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

Thu, 01 Jun 2017 14:45:28 GMT

Its like AM and PM for a day

we are taking only PM data

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

micheledenardi — Thu, 01 Jun 2017 14:46:38 GMT

Sheet1:

LOAD [TGB Name],

Date,

TGB_Counts,

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.