topic Re: Re: Holiday in QlikView

Holiday

Mon, 26 May 2014 07:58:20 GMT

Dear All,

I have two fields

1.With all dates in 2014

2.With all holidays in 2014

All i want is to have a field without Holidays.

(ie., I need to delete the holidays list in the field which has all days.)

How can i do that . Please help me.

Re: Holiday

tresB — Mon, 26 May 2014 08:02:04 GMT

Load

FieldWithAllDates,

FieldHolidays,

If(FieldWithAllDates <> FieldHolidays, FieldWithAllDates) as WorkingDays

Re: Holiday

ashfaq_haseeb — Mon, 26 May 2014 08:04:06 GMT

Hi,

how do you define holiday,

by posting your Excel files can get accurate answer.

anyways try below.

Load HolidayDate,F5,F6 from holiday.xls;

load FullDate, F1,F2

from Full.xls where not exsists(HolidayDateFullDate);

Hope it helped.

Regards

ASHFAQ

Re: Holiday

Mon, 26 May 2014 10:14:48 GMT

Hi Tresesco,

I have two fields, loaded already from two different tables.

It's not working. I also tried using Resident Statement.

But i can able to give only one table name in the Resident.

Is there any other way?

Re: Holiday

ashfaq_haseeb — Mon, 26 May 2014 10:22:14 GMT

Hi, try my solution or post sample files.

Regards

ASHFAQ

Re: Holiday

tresB — Mon, 26 May 2014 10:29:13 GMT

In that case try like:

Table2:
Load * Inline [
HoliDays
1-1-2011
1-1-2014
];
Table1:
Load * Inline [
AllDates
1-1-2011
1-1-2012
1-1-2013
1-1-2014
];

WorkindDates:
Load AllDates as DateWorkingDays

Resident Table1 where not Exists(HoliDays, AllDates);

Re: Holiday

Mon, 26 May 2014 10:43:32 GMT

Hi Pradeep,

Try using LEFT KEEP. Example below assumes you already have 2 tables loaded. You'll need to come up with a common field for the join to work. Below I used a %DateField_Id

[Holidays2014]:

LOAD Num(FieldHolidays) as %DateField_Id,

FieldHolidays

Resident [HolidaysTable];

[WorkingDays2014]:

LEFT KEEP ([Holidays2014])

LOAD Num(WorkingDays) as %DateField_Id,

WorkingDays

Resident [WorkingDaysTable];

Re: Holiday

Anonymous — Mon, 26 May 2014 10:44:02 GMT

hey,

check this

WD:

load * Inline
[
days
1/1/2014
1/2/2014
1/3/2014
1/4/2014
];
HD:
load * Inline
[
days
1/2/2014
];

New:
load count(days) as count,days
Resident WD Group by days;

DROP Table WD;

Final:
load *,'' as i
Resident New Where count=1;

drop Table New;
DROP Field i;

HTH

Ravi N.

Re: Re: Holiday

ashfaq_haseeb — Mon, 26 May 2014 11:01:52 GMT

Hi,

Find the sample application attached.

Note:

I have taken Saturday and Sunday as holidays.

Regards

ASHFAQ

Re: Holiday

Tue, 27 May 2014 03:09:46 GMT

It's works fine.

Thanks all for your great support.