Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Assign ticket scenario

AA Accepted TKT1
AA Informed TKT2
BB Assigned TKT3
BB Pending Supplier TKT4
BB Informed TKT5
BB Informed TKT6
CC Informed TKT7
CC Pending Supplier TKT8
DD Pending Customer TKT9

 

Output:

It should be stored in a excel format using for each loop or in any way.

AA has 2 tickets whereas Accepted 1 and Informed 1 and the incidents are TKT1, TKT2
BB has 4 tickets whereas Assigned 1, Pending Customer 1 and Informed 2 and the incidents are TKT3, TKT4, TKT5, TKT6
CC has 2 tickets whereas Informed 1 and Pending Supplier 1 and the incidents are TKT7, TKT8
DD has 2 tickets whereas Pending Customer 1 and the incidents are TKT9

 

Labels (1)
9 Replies
Highlighted
Partner
Partner

Hi @rammuthiah ,

 

Assuming that the columns have these labels: ID, Status, Ticket.

The following script should work:

 

TICKETS:
LOAD ID, 
     Status, 
     Ticket
FROM
ExampleFile;


LEFT JOIN (TICKETS)
LOAD
	ID,
	Status,
	Count(Status) as NoOfStatus,
    RowNo() as Sort
Resident
	TICKETS
GROUP BY
	ID,
	Status
;

FINAL:
NoConcatenate
LOAD
	ID & ' has ' & NoOfTickets & ' ticket' & If(NoOfTickets>1,'s ',' ') & 'whereas ' & StatusConcat & If(NoOfTickets>1,' and the incidents are ', ' and the incident is ') & Tickets as String
;
LOAD
	ID,
	If(CountofStatus=1,
	
		StatusConcat,
		
		Left(StatusConcat, Index-1) & ' and' & Right(StatusConcat,Len(StatusConcat)-Index)
		
		) as StatusConcat,
		
	NoOfTickets,
	
	Tickets
;
LOAD
	ID,
	Concat(Distinct Status & ' ' & NoOfStatus, ', ', Sort)as StatusConcat,
	Index(Concat(Distinct Status & ' ' & NoOfStatus, ', ', Sort),',', -1) as Index,
	Concat(Distinct Ticket, ', ', Sort) as Tickets,
	Count(Ticket)	as NoOfTickets,
	Count(distinct Status) as CountofStatus
Resident
	TICKETS
GROUP BY
	ID
;

DROP TABLE TICKETS;

STORE FINAL into [Final.csv](txt);
	

 

Hope it helps 😉

Highlighted
MVP
MVP

Assuming that you want to export it from the UI you could create a UI table like this:

 

Dimension:
    ID ( the field containing the AA, BB and so forth...)

Expression/Measure:

=' has ' & Count(Ticket)& ' tickets with status ' & Concat(Status,', ',Ticket#) & ' for the tickets ' & Concat(Ticket,', ',Ticket#)

To get the sorting right make sure that you have a numeric Ticket# or create one in your load script...

 

In QlikView:Annotation 2020-02-02 #1.png

In Qlik Sense:

Annotation 2020-02-02 #2.png

 

Highlighted
Creator II
Creator II

Hi,

Thanks for the effort. Also I tried storing separate file for each ID like

File_AA

Name    Status            Tickets             String

AA            Assigned     TKT1,TKT2     AA has 6 tickets whereas Closed 2 and the incidents are TKT1,TKT2

File_BB

Name    Status            Tickets             String

BB            Assigned     TKT3,TKT4,TKT5     AA has 3 tickets whereas Closed 2 and the incidents are TKT3,TKT4,TKT5 

I tried using for each, but it didn't work.

Also used the maxdate function to fetch only the latest date. But failed to create it.

MaxDate:
Load
File01-dec-19.xlsx
max(date(date#(subfield(Subfield(File,'File',2),'.',1),'DD-MMM-YY'),'DD/MM/YYYY')) as MDate

From PRevTable;

 

let Vmaxdate=Peek('MDate',-1, 'MaxDate');

Latest:

Load

       *

Resident PRevTable where $(Vmaxdate)=MDate;

 

Could you please help me on this?

 

 

 

Highlighted
MVP
MVP

Please share a sample data? For AA how you store status assigned as it has multiple status?

Highlighted
Creator II
Creator II

Above given the sample data

Highlighted
Community Manager
Community Manager

@rammuthiah Are you using Qlik Sense or QlikView? I would like to move this into the correct product forum. 

Sue Macaluso
Highlighted
Creator II
Creator II

I am using Qlik Sense

Highlighted
Creator II
Creator II

AA Accepted TKT1
AA Informed TKT2
BB Assigned TKT3
BB Pending Supplier TKT4
BB Informed TKT5
BB Informed TKT6
CC Informed TKT7
CC Pending Supplier TKT8
DD Pending Customer TKT9

AA has 2 tickets whereas Accepted 1 and Informed 1 and the incidents are TKT1, TKT2
BB has 4 tickets whereas Assigned 1, Pending Customer 1 and Informed 2 and the incidents are TKT3, TKT4, TKT5, TKT6
CC has 2 tickets whereas Informed 1 and Pending Supplier 1 and the incidents are TKT7, TKT8
DD has 2 tickets whereas Pending Customer 1 and the incidents are TKT9

Highlighted
Creator II
Creator II

I tried storing based on Assignee, unexpected token error occurs. May I know why it is not storing the values based on AssigneeAssignee 2.PNGAssignee.PNG