Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please check attached.
Need Help on this issue.
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.
Thanks Aadil for your Reply. But it not giving me the desired output.
Please check attached.
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.
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