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
Also need to store as below
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.
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);
For each _name in fieldvaluelist('Assignee')
[$(_name)]:
Load
'$(_name) which contains Ticket assigned ' & Concat(Ticket, ',') as Message
Resident Data
Where Assignee = '$(_name)';
Store [$(_name)] Into [lib://Desktop/output/$(_name).xls](txt);
Drop table [$(_name)];
Next
