Convert search value to field name for input to another search

[tincupchalice]

Is there a way to take a value from one lookup or search and make it the field name for the other. Example:

| eval idx=3
| lookup ltable1 idx OUTPUT location
| eval idx=5
| lookup ltable1 idx OUTPUT location AS location2
???| eval fname=value(location2) AS FIELD???
| lookup ltable2 location OUTPUT fname

example ltable1

idx,location
1,chi
2,lon,
3,nyc

ltable2

location,chi,lon,nyc
chi,NA,2,3
lon,6,NA,4
nyc,2,5,NA

wanting to Xref the 2 tables like a mileage matrix of sorts, therefore given chi as a value from ltable1 Xref to lon will get me 2.

[lguinn2 · Accepted solution]

I think you can do what you want by having 2 lookups defined, where the results of one lookup are used in the next. Here is a similar question Can I chain fields together between two different lookups? There is a nice discussion there. Here's a summary, tailored to your specific question. Edit props.conf as follows

    ## props.conf
    LOOKUP-ltable1 = lookup_city_name idx OUTPUT location
    LOOKUP-ltable2 = lookup_distance location OUTPUT chi,lon,nyc

But I think that a better way to arrange the second table might be

  from,to,distance
  chi,lon,6
  lon,chi,6
  chi,ny,2
  ny,chi,2

  etc.

This duplicates the entries, because you need an entry for london-to-chicago as well as chicago-to-london, but it makes the logic easier. Unless you are dealing with millions of entries, don't worry about performance.

If you do this, your second lookup could be

  LOOKUP-ltable2 = lookup_distance from, to OUTPUT distance

[tincupchalice]

The second will work given a handful of locations, I guess I was trying to be too clever.