Have this requirement:
P1, KPI1, col1, col2, kpi_value
P2, KPI21, col1, col2, kpi_value
P2, KPI22, col3, col4, kpi_value
P3, KPI31, col1, col2, kpi_value
Now kpi_value for each of the KPI in the first lookup has to be evaluated using the formula from the second lookup.
Have already tried several other answers but to no avail.
| eval kpi_value = [|inputlookup formulae.csv | eval search=formula]
But the above query always picks up the first column from the second lookup and evaluates the value. Not every KPI has those same columns. For example: if the formulae.csv lookup has col1 + col2 in the formula value, it evaluates for all KPIs except for P2, KPI22
I have ready this many times and do not get it. Is your first list of things the content of your first lookup or your data. I am assuming the former. Also, show us some sample event data and what you expect the output to be.
the challenge I see is that you want the subsearch to return exactly one formula for the given KPI and Process based on the parameters in the main search event. The trouble is that the subsearch runs before the main search and hence you simply cannot pass any parameters. You could, however, use a dashboard token in your subsearch, but I don't see how it could help you solve this case. Also, use | return $variable in your subsearch to return the content of your variable: | return $formula
My approach to solve this would be by implementing a custom search command.
The lookup where you have stored kpi related information should have five fields. I'll call them "process", "kpiName", "measurement1", "measurement2", and "kpi_value". These column names cannot change, regardless of where the values in them comes from. Therefore, the SPL for the final result (no need for a formula lookup) would be
... | eval kpi_value = measurement1 + measurement2 | ...
Let's get to the facts: You want to treat every single event differently, drawing your formula from a lookup table. The only way to substitute a formula into a query that I know of is by using a subquery, like you did. Fact: This substitution works globally, because it takes place before your main query starts. If you want to substitute the formula for ALL events alike this works like a charm. If, however, you want to use a different formula based on the P and KPI of every single event, you simply can't because you cannot use a different subquery on every single event.
The only way I see to solve this is by using a custom search command:
Of course you might also try to solve it without implementing some python by using the splunk rest command and assemble the search string using eval.