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: 
rupaliqlik
Creator
Creator

Extract data with excluding holidays

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

stalwar1kaushik.solankirwunderlich

11 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
antoniotiman
Master III
Master III

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
Creator
Creator
Author

Thank You So much.Its working ..

rupaliqlik
Creator
Creator
Author

Thanks.It's working fine.

rupaliqlik
Creator
Creator
Author

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
Master III
Master III

I'm sorry, I don't understand.

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

rupaliqlik
Creator
Creator
Author

Yes

antoniotiman
Master III
Master III

Can You share your result and expected ?

antoniotiman
Master III
Master III

Do You close and Open Doc ?