Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
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

Highlighted
Creator
Creator

Thank You So much.Its working ..

Highlighted
Creator
Creator

Thanks.It's working fine.

Highlighted
Creator
Creator

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

Highlighted
Master III
Master III

I'm sorry, I don't understand.

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

Highlighted
Creator
Creator

Yes

Highlighted
Master III
Master III

Can You share your result and expected ?

Highlighted
Master III
Master III

Do You close and Open Doc ?