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

Store based on names

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

2 Replies
Highlighted
Creator II
Creator II

Got error as below

Assignee 2.PNG

Highlighted
MVP & Luminary
MVP & Luminary

The script in your second post is not the same as your first post.  In you second post, you are missing quotes around '$(_name)'.  In the first post it is correctly quoted. 

-Rob