Merge Master Cal with open and closed dates

[renjithpl]

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

[arsal_90]

Use interval Match function

[MK_QSL]

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.

[its_anandrjs]

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

[its_anandrjs]

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

[preminqlik]

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)