How to match search query results with lookup fields and replace the query result with lookup field value?

[joydeep741]

I have a query which gives results like

COLUMN_1      COLUMN_2
1                        a
2                        b 
3                        c
4                        d
5                        e
6                        f

I have a lookup mylookup.csv which has following data

COLUMN_1      COLUMN_2
1                        1000
3                        1111
6                        2222

My query should search each value in column 1 and If its present in the lookup (mylookup.csv) replace the value of coulmn 2 with the value from column 2 of lookup.

The final result of my query should look like

 COLUMN_1      COLUMN_2
**1                        1000**
2                        b 
**3                        1111**
4                        d
5                        e
**6                        2222**

[Shan]

@joydeep741,

Try below query.
I have just provided an example.
You can consider only this part for your logic | eval tableval2=if(tableval1=lookupval1,lookupval2,tableval2)
I believe you are well aware of how to use lookup and how lookup logic works.

| makeresults 
| eval tableval1=mvappend("1","2","3","4","5","6")
| eval tableval2 =mvappend("a","b","c","d","e","f")
| mvexpand tableval1
| mvexpand tableval2
| table tableval1 tableval2
| appendcols  [| makeresults          
          | eval lookupval1 =mvappend("1","3","6")
          | eval  lookupval2=mvappend("1000","1111","2222")
          | mvexpand lookupval1
          | mvexpand lookupval2
          | table lookupval1 lookupval2
          ]         
          | eval tableval2=if(tableval1=lookupval1,lookupval2,tableval2)
          | table *

Thanks..

[renjith_nair]

@joydeep741,

Try

index="my_index" |fields COLUMN_1,COLUMN_2|lookup mylookup.csv  COLUMN_1 OUTPUT COLUMN_2 as tmp
|eval COLUMN_2=coalesce(tmp,COLUMN_2)|fields - tmp