Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
antonybi
Creator
Creator

Cal_FY

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.

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.)

View solution in original post

16 Replies
tresesco
MVP
MVP

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?

antonybi
Creator
Creator
Author

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??

tresesco
MVP
MVP

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?

antonybi
Creator
Creator
Author

PFA, Source data it contains only EMP, DOJ and DOL

tresesco
MVP
MVP

How do you calculate 15 dates (CalDate) from two dates, logic?

Capture.PNG

antonybi
Creator
Creator
Author

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!!!

tresesco
MVP
MVP

And where there is no DOL you would set the date as Jun-2017?

antonybi
Creator
Creator
Author

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.

tresesco
MVP
MVP

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

]