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|
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 😉
Assuming that you want to export it from the UI you could create a UI table like this:
ID ( the field containing the AA, BB and so forth...)
=' 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 Qlik Sense:
Thanks for the effort. Also I tried storing separate file for each ID like
Name Status Tickets String
AA Assigned TKT1,TKT2 AA has 6 tickets whereas Closed 2 and the incidents are TKT1,TKT2
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.
max(date(date#(subfield(Subfield(File,'File',2),'.',1),'DD-MMM-YY'),'DD/MM/YYYY')) as MDate
let Vmaxdate=Peek('MDate',-1, 'MaxDate');
Resident PRevTable where $(Vmaxdate)=MDate;
Could you please help me on this?
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