Assign ticket scenario

[rammuthiah]

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

[jberna26]

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]

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:

In Qlik Sense:

[rammuthiah]

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]

Above given the sample data

[Sue_Macaluso]

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

[rammuthiah]

I am using Qlik Sense

[rammuthiah]

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]

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