How to convert my lookup field value to an executable formula when i use "foreach"?

[Muller]

Hi, i'm using Splunk since two month and i love it. But i need help.

I have a lot of sensors, sampling per minute. I have a lookup where I can fill in formulas in text format like 'if (sensor1> 200,0, sensor1 / 10)'. Over the period concerned, I want to apply the formula for each sensor every minute.

Here is an expample with four sensors  (row* are the values) :

id_sensor	row 1	row 2	row 3	row 4	formula
ID1	77.2	250	77.5	79.4	=if(value<200,value/10,false)
ID2	227.29	227.18	226.59	227.1	=value/10
ID3	34.1	34.8	35.9	36.1	=(value*9/5)+32

And the results needed

id_sensor	row 1	row 2	row 3	row 4
ID1	7.72	0	7.75	7.94
ID2	22.729	22.718	22.659	22.71
ID3	93.38	94.64	96.62	96.98

I can have a lot of rows and colums ... 
I saw quite a few "close" answers on this forum, but none that I managed to apply.
In particular, I wish to avoid going through a subsearch which would limit the number of exploitable results.

Thank you very much in advance.

[to4kawa]

How many different formulas are there?
If it's about 10 or so, it's easier to identify it by CASE.

[Muller]

Hi To4kawa, and thank you 🙂

The number of formulas will grow over time.
We measure a large number of different equipment and different brands. We must therefore each time imagine the conversion factors necessary to standardize our data.

I will even watch how "Case" works

[to4kawa · Accepted solution]

| tstats count where index=_internal (earliest=-2d@d latest=@d) by sourcetype
| streamstats count as Id
| fillnull col1 col2 col3 col4
| foreach col* [ eval <<FIELD>> = count * (random() % 100 + 1) * <<MATCHSTR>>]
| table Id col*
| rex "(?<comment>(?# this is sample data, from here, foreach and case sample logic.))"
| foreach col* [ eval <<FIELD>> = case(Id=1,if(<<FIELD>> < 200,<<FIELD>>/10,<<FIELD>>),
Id=2,<<FIELD>> / 10,
Id=3,(<<FIELD>> * 9 / 5 ) + 32,
true(), <<FIELD>> )]

Here's how it goes.

[Muller]

What I did with "case".

In my lookup, I created a "case" field and several other fields with different values.
"target" "coef1" "coef2" "coef3" "correction"

For case "0":   <<FIELD>> = '<<FIELD>>'
For case "1":   <<FIELD>> = ('<<FIELD>>' * coef1 + coef2) * coef3
For case ">":      if ('<<FIELD>>'> target, ('<<FIELD>>' * coef1 + coef2) * coef3, correction)
For case "<":      if ('<<FIELD>>' <target, ('<<FIELD>>' * coef1 + coef2) * coef3, correction)
...
...
And it does what I want :).