Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

mergeing two dimension

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_numbercreated_onResoved_date
INC35228662014/02/28 23:51:212014/03/04 15:34:30
INC35228362014/02/28 23:46:222014/03/03 14:48:11
INC35228212014/02/28 23:44:482014/03/03 14:29:12
INC35228042014/02/28 23:43:002014/03/07 18:07:31
INC35227812014/02/28 23:40:522014/03/04 16:16:06
INC35227662014/02/28 23:38:432014/03/04 15:50:04
INC35227522014/02/28 23:37:192014/03/04 15:33:15
INC35227372014/02/28 23:35:322014/03/11 15:10:46
INC35227152014/02/28 23:33:052014/03/04 15:27:51
INC35226962014/02/28 23:31:092014/03/04 15:50:42
INC35226822014/02/28 23:29:092014/03/04 15:37:41
INC35226252014/02/28 23:26:182014/03/04 15:45:14
INC35225872014/02/28 23:20:062014/03/04 15:59:26
INC35225602014/02/28 23:17:022014/03/04 15:53:35
INC35006082014/02/26 20:00:522014/03/05 21:06:22
INC34904352014/02/25 21:04:532014/03/15 07:31:25
INC34867902014/02/25 15:42:272014/03/07 21:53:58
INC34501402014/02/20 01:50:492014/02/27 15:18:33
INC33769692014/02/10 23:09:272014/02/12 15:36:59
INC33746492014/02/10 20:23:542014/02/14 00:45:58
INC33090282014/01/31 15:47:102014/02/06 19:34:13
INC32412802014/01/23 03:45:292014/01/29 22:21:30
INC31652382014/01/13 17:15:262014/01/13 17:21:06
INC31074352014/01/03 22:11:592014/01/09 14:36:37

My output look like this:

Monthexpr1(count.of created tickets)expr2(count.of reolved tickets)
jan43
feb204
mar017

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

Thanks

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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)

View solution in original post

11 Replies
MarcoWedel

Not applicable
Author

I do not see anything wrong.

What do you expect to see?

Greetings

tresesco
MVP
MVP

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

and

canonical-date

MK_QSL
MVP
MVP

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)

avinashelite

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.

ashfaq_haseeb
Champion III
Champion III

Like this.

Regards

ASHFAQ

Not applicable
Author

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

Thanks

MK_QSL
MVP
MVP

Close the thread by selecting appropriate answer...

Not applicable
Author

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)