Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have one file where i extract data from it with T-1 filter.But on monday I extract it with T-2 .As per user's requirement they do not include sundays data or any holidays data(eg.,Public holiday,Independance day,festivals).So currently I'm doing this process manually but I want to schedule this report.Please help me in this kind of scenario.Please find attached sample data.
Regards,
Rup
Hi,
Try this script.
HOLIDAY_MASTER:
LOAD date(date#(HOLIDAYLIST,'DD/MM/YYYY')) as Holidays Inline
[
HOLIDAYLIST
13/03/2017
01/05/2017
26/06/2017
14/08/2017
15/08/2017
25/08/2017
02/10/2017
19/10/2017
20/10/2017
25/12/2017
];
Let vLoop = 0;
Let vLoopCounter = 0;
Let vDate =num(Today());
Do
Data:
Load if(exists(Holidays,FilterDate) or Wildmatch(WeekDay(FilterDate),'*Sun*'), 1,0) as Flag,FilterDate;
Load $(vDate) - $(vLoop) as FilterDate
AutoGenerate 1;
Temp:
Load Flag
Resident Data where Flag=1;
Let vLoopCounter = If(Isnull(Peek('Flag',0,'Temp')),0,1);
Drop table Temp;
If $(vLoopCounter) = 1 then
Drop table Data;
else
Let vFIlterDate = num(peek('FilterDate',0,'Data'));
End IF
Let vLoop = $(vLoop) + 1;
Loop while $(vLoopCounter) = 1;
Directory;
DATA:
LOAD ID,
USERNAME,
DATE(DATE#(LEFT(Date,10),'YYYY-MM-DD'),'DD/MM/YYYY') AS DATE,
Branch
FROM
SAMPLE.xlsx
(ooxml, embedded labels, table is Sheet1) WHERE
DATE(DATE#(LEFT(Date,10),'YYYY-MM-DD'),'DD/MM/YYYY')=$(vFIlterDate);
Above Script will not only look for previous holiday but if there are consecutive holiday and Sunday then it will take care of that also.
Regards,
Kaushik Solanki
Hi Rupali,
if I have understood, like this
SET TimestampFormat='YYYY-MM-DD hh:mmTT';
Directory;
DATA:
LOAD ID,USERNAME,
// DATE(DATE#(LEFT(Date,10),'YYYY-MM-DD'),'DD/MM/YYYY') AS DATE,
Date(Floor(Date)) as DATE,
Branch
FROM
SAMPLE.xlsx
(ooxml, embedded labels, table is Sheet1)
WHERE If(Text(WeekDay(Today()))='Mon',
Date(Floor(Date))=Date(Today()-2), //on monday
Date(Floor(Date))=Date(Today()-1)) //on week days
and Not Exists(HOLIDAYLIST,Date(Floor(Date)));
See Attachment.
Regards,
Antonio
Thank You So much.Its working ..
Thanks.It's working fine.
Dear Antonio,
When today I run this code It should exclude 15th august but it fetches 0 record.I guess It does not execute not existst command.Please help me.
Regards,
Rupali
I'm sorry, I don't understand.
15/08/2017 is in HOLYDAYLIST, then it is excluded.
Yes
Can You share your result and expected ?
Do You close and Open Doc ?