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

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

Got error as below

Assignee 2.PNG

rwunderlich
Luminary Alumni
Luminary Alumni

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