Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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
Luminary Alumni
Luminary Alumni

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".
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 ?