Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have attached one QVD data, i this you will see Plant as WERKS, PRCTR as Profit Centre, MATKL of Material Group and OffDate as Date of Holiday.
You will see the only entries of holidays. if the entry is not There against any possible combination than it means All day working.
Possible Combination are below
Load * Inline
[PRCTR, MATKL, WERKS
2001, 14001, 1001
2002, 14002, 1001
2003, 14003, 1001
2004, 14004, 1001
2005, 14009, 1001
2001, 14010, 1001
2011, 14007, 1001
2012, 14005, 1001
2013, 14006, 1001
2101, 14011, 1001
2102, 14012, 1001
2103, 14013, 1001
2104, 14014, 1001
2105, 14018, 1001
2112, 14015, 1001
2290, 14101, 1001
2291, 14101, 1001
2292, 14101, 1001
2293, 14101, 1001
3001, 14001, 1002
5001, 14001, 1003
6002, 14002, 1004
7001, 14001, 1005
7002, 14002, 1005];
Now my requirement is i want to calculate the No of Working Days of the Month and No of Current Working Days.
e.g.
PRCTR, MATKL, WERKS, OffDate
2001, 14001, 1001, 12.06.2016
that above entry means with above combination No of working Days would be 29 current as well as for the month.
Current Working Days Definition is = if there are four holidays and and one holiday is on 30.06.2016 and you are in 29.06.2016 than current working days would be 29 total days - 3 holidays = 26
and Total working Days = 30 days - 4 holidays = 26
Hope m clear thanks in advance
Please try below script:
TempOffDays:
LOAD DISTINCT
OFF_DT,
MonthYear(OFF_DT) AS MonthYear_Name,
IF(OFF_DT > Today(),0,1) AS CurrentDaysFlag
FROM Stage1_ZCALENDER.qvd (qvd);
OffDays:
LOAD
MonthYear_Name
Count(OFF_DT) as TotalOffDays,
Sum(CurrentDaysFlag) as CurrentOffDays
Resident TempOffDays;
DROP Table TempOffDays;
You can join "OffDays" with you calendar and calculate the Total Working days or Current Working days.
JO dathu,
thanks for revert,
TempOffDays:
LOAD DISTINCT
OFF_DT,
MonthYear(OFF_DT) AS MonthYear_Name,
IF(OFF_DT > Today(),0,1) AS CurrentDaysFlag
FROM Stage1_ZCALENDER.qvd (qvd);
this script giving error, What monthyear function will do????bcoz of it getting error..
hi
with the below script we must have to use group by function right?
OffDays:
LOAD
MonthYear_Name
Count(OFF_DT) as TotalOffDays,
Sum(CurrentDaysFlag) as CurrentOffDays
Resident TempOffDays;