topic Re: Create month from two date columns in QlikView

Create month from two date columns

narender123 — Wed, 25 Mar 2015 09:57:45 GMT

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

Re: Create month from two date columns

MK_QSL — Wed, 25 Mar 2015 10:10:26 GMT

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

Drop Table Temp;

Re: Create month from two date columns

Wed, 25 Mar 2015 13:44:27 GMT

check this file

Re: Create month from two date columns

narender123 — Tue, 21 Apr 2015 11:51:55 GMT

Hi Manish,

Thanks for your reply.

Its working but i have large collection of data record.So its taking too much time to reload.Is there any other way?

Re: Create month from two date columns

MK_QSL — Tue, 21 Apr 2015 17:37:28 GMT

Temp:

Load

employee_id,

start_date,

If(Len(Trim(end_date))=0 or IsNull(end_date), Date(Today()),end_date) as end_date

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

4, 01/01/2015,

];

MinMaxDate:

Load Min(start_date) as MinDate, Max(end_date) as MaxDate Resident Temp;

Let vMinDate = Num(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = Num(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

Final:

Load *, Month(Date) as Month;

Load

$(vMinDate) + IterNo() - 1 as Date

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Inner Join IntervalMatch(Date)

Load start_date, end_date Resident Temp;

Join(Final) Load * Resident Temp;

Drop Table Temp;

Re: Create month from two date columns

narender123 — Wed, 22 Apr 2015 07:20:43 GMT

Hi Manish,

Its showing wrong data as

count(employee_id) for jan month = 31 which is wrong.

it should show count= 2 for jan month.

Thanks,