How do you group results by location and percentage the results?

[davidoking]

I have a search result that is tabled, and now, I am trying to find a result by percentage of the location they visit most, A B or C per person.
Noting that each location A, B or C ,have sub locations designated by numbers, e.g. A1234, A2345, A4567 etc.

I'm trying to find the total % based on primary designation e.g. A*, B* or C*.

I can stats sum the events by sub location, but I can't seem to work out how to group the primary designations as a %.

Example

ID     Patron  Location 
123  Bob       A1234
234  Shirley  A1234
123  Bob      B4264
123  Bob      C7894
234 Shirley  B4356
123 Bob       A4561

hopeful result

                          Location
ID     Patron     A       B    C
123  Bob          X%   X%  X%
234  Shirley    Y%    Y%  Y%

Thoughts?

Thank you all in advance for your help.

[davidoking]

@renjith.nair

Thank you for that. looks great unfortunately it doesn't seem work with the rest of my search. I'll have a look and see if i can modify it a bit. I'm getting a search conflict error.

Thanks again

[renjith_nair]

@davidoking ,

Can you try this and see if it matches your requirements?

| makeresults | eval ID="123,234,123,123,234,123"| makemv ID delim="," | mvexpand ID
| appendcols [| makeresults |  eval Patron="Bob,Shirley,Bob,Bob,Shirley,Bob"| makemv Patron delim=","|  mvexpand Patron]
| appendcols [| makeresults| eval Location="A1234,A1234,B4264,C7894,B4356,A4561"|  makemv Location delim=","| mvexpand Location]
| rename COMMENT as "---All above is for dummy data generation---" 
| eval PrimeLocation=substr(Location,0,1)| eventstats count by Patron,PrimeLocation
| eventstats sum(count) as total| eval perc=round((count/total)*100,2) 
| stats values(eval(if(PrimeLocation=="A",perc,null()))) as A ,values(eval(if(PrimeLocation=="B",perc,null()))) as B,values(eval(if(PrimeLocation=="C",perc,null()))) as C by ID,Patron