Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rammuthiah
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
jberna26
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 😉

petter
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

 

rammuthiah
Creator II
Creator II
Author

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?

 

 

 

Kushal_Chawda

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

rammuthiah
Creator II
Creator II
Author

Above given the sample data

Sue_Macaluso
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
rammuthiah
Creator II
Creator II
Author

I am using Qlik Sense

rammuthiah
Creator II
Creator II
Author

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

rammuthiah
Creator II
Creator II
Author

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