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
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"
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"
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
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.
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
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
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