topic Re: mergeing two dimension in QlikView

mergeing two dimension

Mon, 15 Sep 2014 11:22:39 GMT

I have a requirement where I need to display the no.

of created tkts and no.of resolved tkst by each month.

here is the input table,

Ticket_number	created_on	Resoved_date
INC3522866	2014/02/28 23:51:21	2014/03/04 15:34:30
INC3522836	2014/02/28 23:46:22	2014/03/03 14:48:11
INC3522821	2014/02/28 23:44:48	2014/03/03 14:29:12
INC3522804	2014/02/28 23:43:00	2014/03/07 18:07:31
INC3522781	2014/02/28 23:40:52	2014/03/04 16:16:06
INC3522766	2014/02/28 23:38:43	2014/03/04 15:50:04
INC3522752	2014/02/28 23:37:19	2014/03/04 15:33:15
INC3522737	2014/02/28 23:35:32	2014/03/11 15:10:46
INC3522715	2014/02/28 23:33:05	2014/03/04 15:27:51
INC3522696	2014/02/28 23:31:09	2014/03/04 15:50:42
INC3522682	2014/02/28 23:29:09	2014/03/04 15:37:41
INC3522625	2014/02/28 23:26:18	2014/03/04 15:45:14
INC3522587	2014/02/28 23:20:06	2014/03/04 15:59:26
INC3522560	2014/02/28 23:17:02	2014/03/04 15:53:35
INC3500608	2014/02/26 20:00:52	2014/03/05 21:06:22
INC3490435	2014/02/25 21:04:53	2014/03/15 07:31:25
INC3486790	2014/02/25 15:42:27	2014/03/07 21:53:58
INC3450140	2014/02/20 01:50:49	2014/02/27 15:18:33
INC3376969	2014/02/10 23:09:27	2014/02/12 15:36:59
INC3374649	2014/02/10 20:23:54	2014/02/14 00:45:58
INC3309028	2014/01/31 15:47:10	2014/02/06 19:34:13
INC3241280	2014/01/23 03:45:29	2014/01/29 22:21:30
INC3165238	2014/01/13 17:15:26	2014/01/13 17:21:06
INC3107435	2014/01/03 22:11:59	2014/01/09 14:36:37

My output look like this:

Month	expr1(count.of created tickets)	expr2(count.of reolved tickets)
jan	4	3
feb	20	4
mar	0	17

I am attached sample qvw,I am able to get the expr1 values but..expr2 getting wrong values.

Thanks

Re: mergeing two dimension

MarcoWedel — Mon, 15 Sep 2014 11:28:13 GMT

you might implement a canonical date calendar:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: mergeing two dimension

Mon, 15 Sep 2014 11:28:20 GMT

I do not see anything wrong.

What do you expect to see?

Greetings

Re: mergeing two dimension

tresB — Mon, 15 Sep 2014 11:31:25 GMT

You should ideally create a master calendar in the script to deal with such cases. See: master-calendar

and

canonical-date

Re: mergeing two dimension

MK_QSL — Mon, 15 Sep 2014 11:32:41 GMT

Create below script....

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

T1:

LOAD Ticket_number,

TimeStamp(TimeStamp#(created_on,'YYYY/MM/DD hh:mm:ss')) as Created,

TimeStamp(TimeStamp#(Resoved_date,'YYYY/MM/DD hh:mm:ss')) as Resolved

FROM

[http://community.qlik.com/thread/133327]

(html, codepage is 1252, embedded labels, table is @1);

T2:

Mapping Load

Ticket_number,

Created

Resident T1;

T3:

Mapping Load

Ticket_number,

Resolved

Resident T1;

LinkedDate:

Load

Ticket_number,

'Created' as DateFlag,

ApplyMap('T2',Ticket_number,Null()) as CommonDate

Resident T1;

Concatenate

Load

Ticket_number,

'Resolved' as DateFlag,

ApplyMap('T3',Ticket_number,Null()) as CommonDate

Resident T1;

Join

Load

Ticket_number,

CommonDate,

Month(CommonDate) as Month

Resident LinkedDate;

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

Now Create a Straight Table

Dimension

Month

Expression

COUNT({<DateFlag = {'Created'}>}CommonDate)

COUNT({<DateFlag = {'Resolved'}>}CommonDate)

Re: mergeing two dimension

avinashelite — Mon, 15 Sep 2014 11:33:41 GMT

Hi Rajesh,

Try this script:

Temp:

LOAD Ticket_number,

Month(created_on) as Month1,

Month(Resoved_date) as Month2

FROM

(biff, embedded labels, table is [Sheet1$]);

load Month1 as Month,

count(Ticket_number) as Created_count Resident

Temp Group by Month1;

load Month2 as Month,

count(Ticket_number) as Resolved_count Resident

Temp Group by Month1;

and use the same month in the chart you will the solution, i am not able to reload the file.

Re: mergeing two dimension

ashfaq_haseeb — Mon, 15 Sep 2014 11:37:43 GMT

Like this.

Regards

ASHFAQ

Re: mergeing two dimension

Mon, 15 Sep 2014 11:53:03 GMT

Thanku all...looks all your solutions are fine.

Thanks

Re: mergeing two dimension

MK_QSL — Mon, 15 Sep 2014 11:57:24 GMT

Close the thread by selecting appropriate answer...

Re: mergeing two dimension

Mon, 15 Sep 2014 11:57:40 GMT

Test:

LOAD Ticket_number,

created_on,

Resoved_date

FROM

Harsha.xlsx

(ooxml, embedded labels, table is Sheet1);

Bridgetable:

Load

Ticket_number,

created_on as Date,

Monthname(created_on) as Month,

'CreatedOn' as DataType

Resident Test;

Concatenate (Bridgetable)

Load

Ticket_number,

Resoved_date as Date,

Monthname(Resoved_date) as Month,

'ResovedOn' as DataType

Resident Test;

Expression:

=count({<DataType = {'CreatedOn'}>}Ticket_number)

=count({<DataType = {'ResovedOn'}>}Ticket_number)

Re: mergeing two dimension

ashfaq_haseeb — Mon, 15 Sep 2014 11:57:59 GMT

Hi,

Can you close this thread by selecting appropriate answer.

Regards

ASHFAQ

Re: mergeing two dimension

Anonymous — Mon, 15 Sep 2014 11:59:21 GMT

You can change your model little bit:

T1:

Load

Month1 as Month,

Ticket_number,

'Created' as Source

Resident Sheet1$;

Concatenate(T1)

Load

Month2 as Month,

Ticket_number,

'Resolved' as Source

Resident Sheet1$;

Drop table Sheet1$;