How do I edit my search to group FieldB values by FieldA?

[pradiptam]

My input table is like this

Ticket No   Tower    Status
1           Backup  Resolved
2           Storage   WIP
3           Vmware  Resolved
4           Backup  Pending with Customer
5           Vmware  WIP

My Output table should be like this

Ticket Count    Tower     Status
   2            Backup    Resolved,   Pending with Customer
   2            Vmware    WIP, Resolved
   1            Storage   WIP

I am not getting the desired results as expected while visualizing the same. I am using the following search:

| inputlookup report_data.csv | search  "Create month"=Nov | stats count(Ticket no.)  as "Tickets Received" by Tower , Status

Any help in this regards will be appreciated.

Regards,

Pradipta

[renjith_nair · Accepted solution]

Try this for exact output.

| inputlookup report_data.csv | search  "Create month"=Nov |stats delim="," count(Ticket no.)  as "Tickets Received",values(Status) as Status by Tower|mvcombine Status|sort - "Tickets Received"

[pradiptam]

Thanks to both richgalloway and renjith.nair. I am getting the output as desired by running ' renjith's ' query. When i go to visualization tab it shows me ' tickets reiceived => 2 ' for Backup. Can here modifications be done to show " Resolved, Pending with Customer ".

Can this be done , any suggestions i can try out.

Pradipta

[renjith_nair]

Hello Pradipta, Please mark the answer as an accepted answer if you are happy with that.
Regarding the change in field name, as you might know, you can only plot against an aggregated function or time , you can't plot two strings against each other.

[javiergn]

I would do the following:

| inputlookup report_data.csv 
| search "Create month"=Nov 
| stats count(Status) as "Tickets Received", list(Status) as Status by Tower

[richgalloway]

Try this.

| inputlookup report_data.csv | search "Create month"=Nov | stats count(Ticket no.) as "Tickets Count" by Tower , Status | sort - "Ticket Count" | table "Ticket Count" Tower Status

[pradiptam]

Hi All,

Thanks all for your support , i have resolved my issue. Done the Following steps:

1) Uploaded the File as csv.
2) Created a Data Model, based on the same.
3) Created a pivot and got my desired output. Not using inputlookup, using SPL.

Regards,
Pradipta