topic Merge Master Cal with open and closed dates in QlikView

Merge Master Cal with open and closed dates

renjithpl — Sun, 13 Apr 2014 09:43:41 GMT

Hi All,

I have a table like below:

ID	Status	Date opened	Date closed
8000003001	Closed	05-Mar-2014	01-Apr-2014
8000003102	In Process	17-Mar-2014	-
8000003122	In Process	18-Mar-2014	-
8000003121	In Process	18-Mar-2014	-
8000003204	In Process	26-Mar-2014	-
8000003203	Proposed Solution	26-Mar-2014	-

I am auto generating dates from 1-jan-2014 till today().

Trying to merge this dates with the [Date opened] and [Date closed]

I want to get an output table something like below:

Day	opened tickets	closed tickets
05-Mar-2014	1	0
17-Mar-2014	1	0
18-Mar-2014	2	0
26-Mar-2014	2	0
01-Apr-2014	0	1

I am not able to get April entry which has closed ticket.

Expression of opened tickets = count([Date opened])

Expression of closed tickets = count([Date closed])

Please do let me know if additional infor required.

Regards

Ren

Re: Merge Master Cal with open and closed dates

arsal_90 — Sun, 13 Apr 2014 10:05:11 GMT

Use interval Match function

Re: Merge Master Cal with open and closed dates

MK_QSL — Sun, 13 Apr 2014 10:57:05 GMT

Use below script

=================

T1:

Load

ID,

Status,

Date(Date#([Date opened],'DD-MMM-YYYY')) as [Date Opened],

Date(Date#([Date closed],'DD-MMM-YYYY')) as [Date Closed]

Inline

[

ID, Status, Date opened, Date closed

8000003001, Closed, 05-Mar-2014, 01-Apr-2014

8000003102, In Process, 17-Mar-2014, -

8000003122, In Process, 18-Mar-2014, -

8000003121, In Process, 18-Mar-2014, -

8000003204, In Process, 26-Mar-2014, -

8000003203, Proposed Solution, 26-Mar-2014, -

];

Opened:

Mapping Load

ID,

[Date Opened]

Resident T1;

Closed:

Mapping Load

ID,

[Date Closed]

Resident T1;

NoConcatenate

Final:

Load

ID,

ApplyMap('Opened',ID,Null()) as Date,

'Open' as DataType

Resident T1;

Load

ID,

ApplyMap('Closed',ID,Null()) as Date,

'Close' as DataType

Resident T1;

===========================================

Now create a straight table

Dimension

Date

and Tick Suppress When Value is NULL

Expressions

Open Tickets

Count({<DataType = {'Open'}>}ID)

Close Tickets

Count({<DataType = {'Close'}>}ID)

===============

File enclosed for your reference.

Re: Merge Master Cal with open and closed dates

its_anandrjs — Sun, 13 Apr 2014 11:10:04 GMT

Try to load your table like

LOAD 'Open Tickets' as TicketFlag,ID, Status,[Date opened] as Date;

LOAD * INLINE [

ID, Status, Date opened

8000003001, Closed, 05-Mar-2014

8000003102, In Process, 17-Mar-2014

8000003122, In Process, 18-Mar-2014

8000003121, In Process, 18-Mar-2014

8000003204, In Process, 26-Mar-2014

8000003203, Proposed Solution, 26-Mar-2014

];

Concatenate

LOAD 'Closed Tickets' as TicketFlag,ID, Status,[Date closed] as Date;

LOAD * INLINE [

ID, Status, Date closed

8000003001, Closed, 01-Apr-2014

8000003102, In Process, -

8000003122, In Process, -

8000003121, In Process, -

8000003204, In Process, -

8000003203, Proposed Solution, -

];

And in front end take a straight table

Dimension >> =if(Len(Date)>1,Date)

Expression >> Count({<TicketFlag={'Open Tickets'}>} ID)

Expression >> Count({<TicketFlag={'Closed Tickets'}>} ID)

And connect this table with Master Calendar

Let varMinDate = Num(Makedate(2014,1,1));

Let varMaxDate = Num(Makedate(Year(today()),Month(today()),Day(today())));

Datefield:

LOAD

date($(varMinDate)+IterNo()-1) AS Datefield,

date($(varMinDate)+IterNo()-1,'DD-MMM-YYYY' ) AS Date

AUTOGENERATE (1)

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Regards

Re: Merge Master Cal with open and closed dates

its_anandrjs — Sun, 13 Apr 2014 11:43:37 GMT

After loading table and when creating straight table chart, if in date column null values shown then use dimension like

Take a straight table

Dimension >> =if(Len(Date)>1,Date) and Suppress when value null

Expression >> Count({<TicketFlag={'Open Tickets'}>} ID)

Expression >> Count({<TicketFlag={'Closed Tickets'}>} ID)

Regards

Re: Merge Master Cal with open and closed dates

preminqlik — Sun, 13 Apr 2014 14:18:22 GMT

LOAD

'Opened' as OPEN_CLOSED_FLAG,

ID,

Status,

DateOpened,

DateOpened as %CommonCalendarLink,

DateClosed as DateClosed_Ref

resident orginal;

concatenate

LOAD

'Closed' as OPEN_CLOSED_FLAG,

ID,

Status,

DateClosed,

DateClosed as %CommonCalendarLink

resident Orginal;

drop table Orginal;

---> Now connect the Master Calendar with %CommonCalendarLink

in front end take Date field of Master calendar

and expression :

For Opened :

Count({<OPEN_CLOSED_FLAG={'Opened'}>}DateOpened)

For Closed :

Count({<OPEN_CLOSED_FLAG={'Closed'}>}DateClosed)