Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
narender123
Specialist
Specialist

Create Month from "between(start date and end_date)" fields.

Hi Team, I have a table like:

employee_id              start_date                    end_date

1                                01/01/2015              05/03/2015
2                                17/03/2015              25/04/2015

3                                15/03/2015              018/04/2015

- - - -  - - - - - -                - - - - - -  -                    - - - - -  -  -


I want to count "employee_id" based on the month between start_date and end_date.So how i can make a month calender started from "start_date"  & ended to "end_date"???? As shown in above table the month should be jan,feb,mar for employee_id=1. if i will select jan or feb or mar then it will only select employee_id=1.How i can create month calender that work in such way???? Thanks in advance. Narender

9 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Maybe something like this:

LOAD *, date(monthstart(start_date, IterNo()-1),'MMM') as month

WHILE monthstart(start_date, IterNo()-1) <= end_date;

LOAD

     employee_id,

     date#(start_date,'DD/MM/YYYY') as start_date,

     date#(end_date,'DD/MM/YYYY') as end_date

FROM ....    


talk is cheap, supply exceeds demand
narender123
Specialist
Specialist
Author

Hi Gysbert,

Thanks for your reply.

I have already tried  this in past like:

Temp:

Load * Inline

[

  employee_id, start_date, end_date

  1, 01/01/2015, 05/03/2015

  2, 17/03/2015, 25/04/2015

  3, 15/03/2015, 18/04/2015

];

NoConcatenate

Final:

Load

  employee_id,

  start_date,

  end_date,

  Month(AddMonths(start_date, IterNo() - 1)) as Month

Resident Temp

While Month(AddMonths(start_date, IterNo() - 1)) <= Month(end_date);

As my data is too much large this will take huge time which is not possible for me.I think you have suggested the same?? or it will also time consuming?

Pls suggest me .

Thanks.

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

You don't explicitly make dates from the date string values in your inline load. That could lead to problems.

The while clause could lead to missing data. For example with start date 15/11/2014 and end date 28/03/2015. The month number of the end date is lower than the month of the start date. The while clause would always return false for this combination so no records would be added to the table.


talk is cheap, supply exceeds demand
narender123
Specialist
Specialist
Author

Hi Gysbert,

Yes, you are right.For this, can i do this

While num(AddMonths(start_date, IterNo() - 1)) <= num(end_date);


Or any other way to sort out this?



Thanks

narender123
Specialist
Specialist
Author

Hi Gysbert,

Can i use this:

While num(AddMonths(start_date, IterNo() - 1)) <= num(end_date);


pls assist.



Thanks

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

You can use it. But I can't guarantee it will work. I don't know your application well enough to tell with certainty.


talk is cheap, supply exceeds demand
CELAMBARASAN
Partner - Champion
Partner - Champion

May be you could try the below approach

Employee:

LOAD *, MonthStart(start_date) AS MonthStart_StartDate,MonthEnd(end_date) AS MonthEnd_EndDate INLINE [

    employee_id,start_date,end_date

    1,01/01/2015,05/03/2015

    2,17/03/2015,25/04/2015

    3,15/03/2015,18/04/2015

];

Calendar:

LOAD

  Date(AddMonths(MinDate,IterNo()-1)) AS Date,

  MonthName(MinDate,IterNo()-1) AS MonthName

While Date(AddMonths(MinDate,IterNo()-1)) <= MaxDate;

LOAD

  MonthStart((Min(MonthStart_StartDate))) AS MinDate,

  MonthEnd(Max(MonthEnd_EndDate)) AS MaxDate

Resident Employee;

Left Join IntervalMatch(Date)

LOAD

  MonthStart_StartDate,MonthEnd_EndDate

Resident Employee;

narender123
Specialist
Specialist
Author

Thanks Celambarasan Adhimulam.

But i didnt get your point.

Thanks.

narender123
Specialist
Specialist
Author

Thanks Gysbert.