Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aveeeeeee7en
Specialist III
Specialist III

Exclude and Count Number of Holidays Lying between 2 dates

I want to exclude Holidays by Counting them. I cant remove All Holiday Dates from Main Table because on different-2 Branch Ids different Holidays lie. So, if i exclude all these Holiday Dates it will cause a Problem in Calculation which is to be made on the basis of ORGANIZATION_NAME_OF_ENGINEER.

When you Select a unique TICKETNUMBER its Creation Date, Completion Date & Assigned Date appears.

So, what i need is to Count Number of Holidays ORGANIZATION_NAME_OF_ENGINEER wise for unique Tickets lying between

Creation Date, Completion Date & Assigned Date.

Note : 1) If Receive Type='WALK-IN' then Calculate number of Holidays coming between CREATIONDATE & ENGINEER_COMPLETED_DATE

2) If Receive Type='COURIER' then Calculate number of Holidays coming between ENGINEER_COMPLETED_DATE &

ENGINEER_ASSIGN_DATE

What I exactly Want:

I need to calculate the Holidays Lying field.

TICKETNUMBER

ORGANISATION_NAME_OF_ENGINEER

CREATIONDATE

ENGINEER_ASSIGN_DATE

ENGINEER_COMPLETED_DATE

Receive Type

Holidays Lying

Holiday Dates

CD1300000310992

Level III Gurgaon

02/08/2013

05/08/2013

27/08/2013

WALK-IN

3

9/8/2013, 15/8/1013, 20/8/2013

Dates Coming Between CREATIONDATE & ENGINEER_COMPLETED_DATE for Walk-in Cases

CD1300000311165

ASF Chennai

03/08/2013

03/08/2013

14/08/2013

WALK-IN

1

9/8/2013

CD1300000311734

Level IV Bangalore

05/08/2013

05/08/2013

23/08/2013

WALK-IN

3

9/8/2013, 15/8/1013, 20/8/2013

CD1300000311773

Level IV Delhi

05/08/2013

06/08/2013

12/08/2013

WALK-IN

1

9/8/2013

CD1300000311830

Level IV Bangalore

05/08/2013

06/08/2013

19/08/2013

WALK-IN

2

9/8/2013, 15/8/1013

CD1300000311851

ASF Hyderabad

05/08/2013

05/08/2013

10/08/2013

WALK-IN

1

9/8/2013

CD1300000311860

Level IV Mumbai

05/08/2013

05/08/2013

24/08/2013

WALK-IN

3

9/8/2013, 15/8/1013, 20/8/2013

CD1300000311862

Level IV Mumbai

05/08/2013

05/08/2013

06/08/2013

WALK-IN

0

CD1300000311920

Level IV Bangalore

06/08/2013

08/08/2013

23/08/2013

COURIER

3

9/8/2013, 15/8/1013, 20/8/2013

Dates Coming Between ENGINEER_ASSIGN_DATE & ENGINEER_COMPLETED_DATE for Courier Cases

CD1300000312436

ASF Chennai

07/08/2013

08/08/2013

22/08/2013

COURIER

3

9/8/2013, 15/8/1013, 20/8/2013

CD1300000313761

ASF Patna

12/08/2013

16/08/2013

23/08/2013

COURIER

1

20/8/2013

Kindly help me on this. Thank You in Advance.

1 Solution

Accepted Solutions
Anonymous
Not applicable

Please check attached.

View solution in original post

6 Replies
aveeeeeee7en
Specialist III
Specialist III
Author

Need Help on this issue.

Anonymous
Not applicable

Hope i have understood your issue. Here is a solution.

Try this.

First, Load your MainTable.

Second, Load your HolidaysTable.

(Your QVD has duplicate records; do a distinct load and then try; or use RECNO() to identify the duplicates)

and then use the below script.

T2:

IntervalMatch (HOLIDAY_DATE)

LOAD

CREATIONDATE as CREATIONDATE_T,

ENGINEER_COMPLETED_DATE as ENGINEER_COMPLETED_DATE_T

Resident MainTable;

Join (MainTable)

LOAD

HOLIDAY_DATE as HOLIDAY_DATE_TO_COUNT,

ENGINEER_COMPLETED_DATE_T as ENGINEER_COMPLETED_DATE Resident T2;

DROP Table T2;

On the chart, you can just use ORGANISATION_NAME_OF_ENGINEER as dimension and count(HOLIDAY_DATE_TO_COUNT) as your expression.

hope that helps.

aveeeeeee7en
Specialist III
Specialist III
Author

Thanks Aadil for your Reply. But it  not giving me the desired output.

Anonymous
Not applicable

Please check attached.

aveeeeeee7en
Specialist III
Specialist III
Author

Thanks Aadil. It is working perfectly fine.

But i want 2 things :

1) Holidays to be excluded Branch wise - Right now it is excluding all Holidays coming between the dates.

2) There is a field called Receive Type :

    a) If Receive Type is 'WALK-IN' then search Holidays between the Range CREATIONDATE and       ENGINEER_COMPLETED_DATE.

b) If Receive Type is 'COURIER' then search Holidays between the Range ENGINEER_ASSIGN_DATE and ENGINEER_COMPLETED_DATE.

Kindly Help.

Anonymous
Not applicable

Glad it helped.

I guess then you need to run the interval match twice with the condition in where clause. Then do the join and count the respective column on the pivot.

I don't see a branch anywhere except the BRANCH_ID.

Thanks,
Aadil