Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Hi All,

I have a Data like below table

TGB NameDateTGB_Counts
Bala_AMI04/06/2017 PM4
Bala_AMI04/07/2017 PM5
Bala_AMI04/08/2017 PM9
Bala_AMI04/09/2017 PM2
Metro_AMI04/06/2017 PM3
Metro_AMI04/07/2017 PM6
Metro_AMI04/08/2017 PM8
Metro_AMI04/09/2017 PM12
Cheta_AMI04/06/2017 PM7
Cheta_AMI04/07/2017 PM15
Cheta_AMI04/08/2017 PM13
Cheta_AMI04/09/2017 PM2

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

1 Solution

Accepted Solutions
micheledenardi
Specialist II
Specialist II

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.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

3 Replies
sunny_talwar

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

Not applicable
Author

Its like AM and PM for a day

we are taking only PM data

micheledenardi
Specialist II
Specialist II

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.

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.