Extract data with excluding holidays

[rupaliqlik]

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

stalwar1‌ kaushik.solanki‌ rwunderlich‌

[kaushiknsolanki]

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

[antoniotiman]

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

[rupaliqlik]

Thank You So much.Its working ..

[rupaliqlik]

Thanks.It's working fine.

[rupaliqlik]

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

[antoniotiman]

I'm sorry, I don't understand.

15/08/2017 is in HOLYDAYLIST, then it is excluded.

[rupaliqlik]

Yes

[antoniotiman]

Can You share your result and expected ?

[antoniotiman]

Do You close and Open Doc ?