topic Re: Extract data with excluding holidays in QlikView

Extract data with excluding holidays

rupaliqlik — Wed, 25 Nov 2020 16:16:04 GMT

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‌

Re: Extract data with excluding holidays

kaushiknsolanki — Tue, 15 Aug 2017 08:08:09 GMT

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());

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

Re: Extract data with excluding holidays

antoniotiman — Tue, 15 Aug 2017 10:10:38 GMT

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

Re: Extract data with excluding holidays

rupaliqlik — Tue, 15 Aug 2017 14:54:53 GMT

Thank You So much.Its working ..

Re: Extract data with excluding holidays

rupaliqlik — Tue, 15 Aug 2017 14:55:23 GMT

Thanks.It's working fine.

Re: Extract data with excluding holidays

rupaliqlik — Wed, 16 Aug 2017 06:28:34 GMT

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

Re: Extract data with excluding holidays

antoniotiman — Wed, 16 Aug 2017 06:50:05 GMT

I'm sorry, I don't understand.

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

Re: Extract data with excluding holidays

rupaliqlik — Wed, 16 Aug 2017 06:57:02 GMT

Yes

Re: Extract data with excluding holidays

antoniotiman — Wed, 16 Aug 2017 07:01:05 GMT

Can You share your result and expected ?

Re: Extract data with excluding holidays

antoniotiman — Wed, 16 Aug 2017 07:06:01 GMT

Do You close and Open Doc ?

Re: Extract data with excluding holidays

rupaliqlik — Wed, 16 Aug 2017 07:25:43 GMT

Dear Antonio,

Data should be come for 14th august.In india Today is 16th August and My user want to see only Working days data.On 15th august we have holiday.

Re: Extract data with excluding holidays

antoniotiman — Wed, 16 Aug 2017 07:37:46 GMT

Try this,

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(0)))='Mon' or Exists(HOLIDAYLIST,Today(0)-1),
Date(Floor(Date))=Date(Today(0)-2), //on monday
Date(Floor(Date))=Date(Today(0)-1)) //on week days
and Not Exists(HOLIDAYLIST,Date(Floor(Date)));