I have a vehicle fleet lookup table like:
vehicle_id,vehicle_year,vehicle_type,vehicle_ends,vehicle_agency,vehicle_livery
1,1912,"A",2,"San Francisco Municipal Railway","San Francisco Municipal Railway"
228,1934,"",2,"Blackpool Tramways","Blackpool Tramways"
578,1896,"G",2,"Market Street Railway","Market Street Railway"
737,1952,"PCC",1,"Brussels, Belgium","Zurich, Switzerland"
1807,1928,"Milan",1,"Milan, Italy","Milan, Italy (1928)"
A transforms.conf
file like:
[Historic_fleet]
filename = Historic_fleet.csv
And a props.conf
file like:
[NextBus]
category = Custom
disabled = false
LOOKUP-fleet = Historic_fleet vehicle_id
This all works fine. However, there are occasionally other vehicles out that are busses. They have their own vehicle_id
s that are not in the lookup table. What I want is to have any vehicle_id
that is not found to map to a vehicle_type=bus
. I'd like to do this automatically so I don't have to do it for every query I write.
I'm aware that a lookup table can have a default value, but how do you specify that a default value of, say, bus
, is supposed to go into the vehicle_type
field?
Can I do this with static tables? Or must I resort to using an external lookup Python script?
There is nothing really automatic that doesn't overkill like @lguinn said, but you can do this manually:
... | lookup YourLookup | fillnull value="bus" vehicle_type
Or like this:
... | eval vehicle_type="bus" | lookup YourLookup
Or even like this:
... | lookup YourLookup | eval vehicle_type=coalesce(vehicle_type, "bus")
You can add this to a macro (e.g. default_to_bus_vehicle_lookup
) to make it centrally manageable and "automaticish".
The default value that you specify is placed into all fields that are returned from the lookup, when there is no match. Default values work with static tables.
I highly recommend setting a default value for lookup tables. It is an excellent solution to this sort of problem.