topic Re: Need Help.....? in QlikView

Need Help.....?

Wed, 19 Feb 2014 16:45:16 GMT

HI All,

How to calculate Holidays and Sunday in Between 2 days (In_Date and Out_Date)

calculate Holidays count...........

Table1:

LOAD * INLINE [

Token_Num, In_Date, Out_Date

100, 1/1/2014, 1/5/2014

101, 1/9/2014, 1/15/2014

102, 1/12/2014, 1/23/2014

103, 1/22/2014, 1/31/2014

104, 1/25/2014, 2/5/2014

105, 2/6/2014, 2/10/2014

106, 2/12/2014, 2/14/2014

107, 2/15/2014, 2/22/2014

108, 2/23/2014, 2/27/2014

109, 3/1/2014, 3/5/2014

110, 3/10/2014, 3/15/2014

];

Holiday_List:

Date	Holiday List
7/1/2014	Guru Govind Singh Jayanti
14/1/2014	Pongal
26/1/2014	Republic Day
31/1/2014	Chinese New Year
4/2/2014	Vasant Panchami
9/2/2014	Guru Ravidas Jayanti
14/2/2014	Valentine's Day

Thanks in Advance................

Re: Need Help.....?

agilos_mla — Wed, 19 Feb 2014 21:29:13 GMT

Try this :

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='D/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

Table1:

LOAD * INLINE [

Token_Num, In_Date, Out_Date

100, 1/1/2014, 1/5/2014

101, 1/9/2014, 1/15/2014

102, 1/12/2014, 1/23/2014

103, 1/22/2014, 1/31/2014

104, 1/25/2014, 2/5/2014

105, 2/6/2014, 2/10/2014

106, 2/12/2014, 2/14/2014

107, 2/15/2014, 2/22/2014

108, 2/23/2014, 2/27/2014

109, 3/1/2014, 3/5/2014

110, 3/10/2014, 3/15/2014

];

Holiday_List:

LOAD Date#(Date, 'D/M/YYYY') AS Date_Off, *; // Sync Dates format

Load * Inline [

Date, Holiday

7/1/2014, Guru Govind Singh Jayanti

14/1/2014, Pongal

26/1/2014, Republic Day

31/1/2014, Chinese New Year

4/2/2014, Vasant Panchami

9/2/2014, Guru Ravidas Jayanti

14/2/2014, Valentine's Day

];

Left join (Table1)

IntervalMatch (Date_Off) LOAD In_Date, Out_Date Resident Table1;

Table1_Final:

LOAD Token_Num, In_Date, Out_Date,

count(Token_Num) AS NumberOfDaysOff

Resident Table1

Group by Token_Num, In_Date, Out_Date;

Drop Table Table1;

Re: Need Help.....?

fernando_tonial — Wed, 19 Feb 2014 21:34:17 GMT

Try the function NetWorkDays()

NetWorkDays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

NetWorkDays('2007-02-19', '2007-03-01') returns 9

NetWorkDays('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

Best Regards.

Tonial.

Re: Need Help.....?

Thu, 20 Feb 2014 14:09:41 GMT

HI Michael,

i want calculate no.of holidays Count

Suppose Example

ID Date1 Date2 Count_of_Holidays

101, 1/9/2014, 1/15/2014 4 (Sat and Sun=2 and 7, 14 holidays)

102, 1/13/2014, 1/23/2014 2 (Sat and Sun=2)

Hope you understand............

Re: Need Help.....?

fernando_tonial — Wed, 26 Feb 2014 21:43:19 GMT

You try this:

(Date2-Date1)-NetWorkDays(Date1,Date2,'7/1/2014','14/1/2014','26/1/2014','31/1/2014','4/2/2014','9/2/2014','14/2/2014')

Best Regards.

Tonial.