How to use Foreach for multiple columns and multiple rows?

[ChioNeng]

Hello, I'm having trouble figuring out how to use foreach + eval getting the difference of the fields.

I have something like this: 

 
You can use this search to obtained the above result: 

| makeresults
| eval Country="PH"
| eval "2020-01 Actual"=1
| eval "2020-01 Forecast"=2
| eval "2020-02 Actual"=5
| eval "2020-02 Forecast"=4
| eval "2020-03 Actual"=50
| eval "2020-03 Forecast"=20
| append
[| makeresults
| eval Country="IND"
| eval "2020-01 Actual"=3
| eval "2020-01 Forecast"=3
| eval "2020-02 Actual"=2
| eval "2020-02 Forecast"=2
| eval "2020-03 Actual"=40
| eval "2020-03 Forecast"=23
]
| append
[| makeresults
| eval Country="SG"
| eval "2020-01 Actual"=2
| eval "2020-01 Forecast"=4
| eval "2020-02 Actual"=1
| eval "2020-02 Forecast"=9
| eval "2020-03 Actual"=30
| eval "2020-03 Forecast"=53 ]
| fields - _time

And I'm trying to use foreach/eval to get this:

Thanks in advance 🙂 

[renjith_nair · Accepted solution]

Try

| makeresults
| eval Country="PH"
| eval "2020-01 Actual"=1
| eval "2020-01 Forecast"=2
| eval "2020-02 Actual"=5
| eval "2020-02 Forecast"=4
| eval "2020-03 Actual"=50
| eval "2020-03 Forecast"=20
| append
[| makeresults
| eval Country="IND"
| eval "2020-01 Actual"=3
| eval "2020-01 Forecast"=3
| eval "2020-02 Actual"=2
| eval "2020-02 Forecast"=2
| eval "2020-03 Actual"=40
| eval "2020-03 Forecast"=23
]
| append
[| makeresults
| eval Country="SG"
| eval "2020-01 Actual"=2
| eval "2020-01 Forecast"=4
| eval "2020-02 Actual"=1
| eval "2020-02 Forecast"=9
| eval "2020-03 Actual"=30
| eval "2020-03 Forecast"=53 ]
| fields - _time
| eval prev=0
| foreach 20* [eval <<FIELD>>_DIFF='<<FIELD>>' - prev|eval prev='<<FIELD>>']
| fields - *Actual_DIFF,prev
| rename "* Forecast_DIFF" as "* Difference"

[ChioNeng]

@renjith_nair thanks for the answer 🙂