SO I have a data set
User Vehicle
User_a Car
User_b Car
User_a MotorBike
User_c MotorBike
User_d Car
User_c Bicycle
User_a Bicycle
User_c Scooter
User_e Car
What I need is to be able to run a search against this type of dataset and pull out only one return per username based upon those with a CAR, then Motorbike, then bicycle then scooter.
But I only need ONE return for any given user - if they have all four - based upon priority they are reported as a car owner. If they only have two or three of the four, they only get reported as the owner of the highest priority vehicle.
I'm currently doing a search cars, score 1pt, append motobike score 2pt, and so on but that is slow on a big datasaet.
| eval vehicle_score=case(Vehicle="Car",1,Vehicle="MotorBike",2,Vehicle="Bicycle",3,Vehicle="Scooter",4)
| stats min(vehicle_score) as vehicle_score by User
| eval Vehicle=case(vehicle_score=1,"Car",vehicle_score=2,"MotorBike",vehicle_score=3,"Bicycle",vehicle_score=4,"Scooter")
| eval vehicle_score=case(Vehicle="Car",1,Vehicle="MotorBike",2,Vehicle="Bicycle",3,Vehicle="Scooter",4)
| stats min(vehicle_score) as vehicle_score by User
| eval Vehicle=case(vehicle_score=1,"Car",vehicle_score=2,"MotorBike",vehicle_score=3,"Bicycle",vehicle_score=4,"Scooter")
Thanks - simple when you think about it, I was doing a similar thing but allocating a score as I appended each loading, then score<= the results.
But this is eloquent - Thanks implemented and working perfectly
I'd eval a var with the point values in a case statement car highest number and so on. Then I'd stat the results of the search max(value) by user, afterward you could then eval a new field with the text values based on the number returned in the max(value). Might not be efficient but would be easy to read and keep updated if new vehicle types are added.