Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
May I ask what does PM in your date field stands for?
Its like AM and PM for a day
we are taking only PM data
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.