Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aveeeeeee7en
Valued Contributor 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.

Tags (3)
1 Solution

Accepted Solutions
aadilmadarveet
Valued Contributor

Re: Re: Re: Exclude and Count Number of Holidays Lying between 2 dates

Please check attached.

6 Replies
aveeeeeee7en
Valued Contributor III

Re: Exclude and Count Number of Holidays Lying between 2 dates

Need Help on this issue.

aadilmadarveet
Valued Contributor

Re: Re: Exclude and Count Number of Holidays Lying between 2 dates

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
Valued Contributor III

Re: Re: Exclude and Count Number of Holidays Lying between 2 dates

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

aadilmadarveet
Valued Contributor

Re: Re: Re: Exclude and Count Number of Holidays Lying between 2 dates

Please check attached.

aveeeeeee7en
Valued Contributor III

Re: Exclude and Count Number of Holidays Lying between 2 dates

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.

aadilmadarveet
Valued Contributor

Re: Exclude and Count Number of Holidays Lying between 2 dates

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

Community Browser