Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have created FY Calendar based on Emp DOJ and DOL.
Created caldates for each employee from DOJ to DOL
If Emp has no DOL then consider to till date.
This is working fine.
But performance wise I want reduce the data.I don't want to load all the cal dates.
how to accomplish this requirement?
PFB for your reference with my sample data.
NewCalDate which is Highlighted in Yellow is my desired output instead of load all the cal dates.
Any Ideas kindly Suggest.
Try something like:
Input:
Load
EMP,
Date#(DOJ,'MM/DD/YYYY') as DOJ,
Date#(DOL,'MM/DD/YYYY') as DOL
Inline [
EMP, DOJ, DOL
3359, 12/1/2015, 12/3/2016
3501, 1/1/2017, NA
A210, 8/26/2016, NA
];
NoConcatenate
FM:
//First broken month
Load
EMP,
DOJ,
DOL,
Date(If(Day(DOJ)=1, DOL,Date(DOJ+IterNo()-1))) as CalDate
Resident Input While if(Day(DOJ)=1,IterNo()=1,DOJ+IterNo()-1<=RangeMin(MonthEnd(DOJ),DOL));
LM:
//Last broken month
Load
EMP,
DOJ,
DOL,
Date(MonthStart(DOL)+IterNo()-1) as CalDate
Resident Input While MonthStart(DOL)+IterNo()-1<=DOL;
CM:
//Complete Months
Load
EMP,
DOJ,
DOL,
MonthEnd(Addmonths(DOJ,IterNo()-1)) as CalDate
Resident Input While MonthEnd(Alt(DOL,today()))>=MonthEnd(AddMonths(DOJ,iterno()-1));
Drop Table Input;
Note: Try to grasp the idea of the code instead of just copy-pasting it, so that if it needs a small adjustment in your context you can do it (I write so, because I guess that you would need to tweak a bit after rigorous testing on output. No pun intended.)
Is there any logic how you arrived to your desired dates, or they are just coming from a field/list may from a transaction table? How does your script look like now?
Hi Tresesco,
Thanks for your quick response.
CalDate which I derived based on the employee DOJ and DOL.
It generates all the dates based on every employee DOJ and DOL.
But performance wise I want to reduce the data.I don't want to load all the dates as per NewCalDate which I have highlighted in yellow.
Is it possible to achieve this requirement??
Let's talk about what you have and what you want, not what you derived that you don't want. How does your source table look like and how do you want the output to be looked like?
PFA, Source data it contains only EMP, DOJ and DOL
How do you calculate 15 dates (CalDate) from two dates, logic?
The logic is,
DOJ of Emp 3359 is 1-Dec-2015 and DOL is 03-Dec-2016
With my existing, script I can generate the CalDates between 1-Dec-2015 to 03-Dec-2016.
But I want to generate dates as like below
Create only Monthend date of these below months instead of all the dates
Dec-2015
Jan-2016
Feb-2016
Mar-2016
Apr-2016
May-2016
Jun-2016
Jul-2016
Aug-2016
Sep-2016
Oct-2016
Nov-2016
And now Day wise
1-Dec-2016
2-Dec-2016
3-Dec-2016
Hope you got this logic!!!
And where there is no DOL you would set the date as Jun-2017?
That is I have restricted to Jun-2017
Usually
If Emp has no DOL It indicates is in active, For this case, consider from DOJ to till date(today()).So it is obviously 08-Aug-2017.
Try like:
Load
EMP,
DOJ,
DOL,
MonthEnd(Addmonths(DOJ,IterNo()-1)) as CalDate
While MonthEnd(Alt(Date#(DOL,'MM/DD/YYYY'),today()))>=MonthEnd(Alt(AddMonths(DOJ,iterno()-1))) ;
Load * Inline [
EMP, DOJ, DOL
3359, 12/1/2015, 12/3/2016
3501, 1/1/2017, NA
A210, 8/26/2016, NA
]