Hi All,
Here is my requirement:
I have 100 values (abc1,def1,....etc) in lookup1 and 100 values in lookup2 (ABC1,DEF1....etc) and I want to do this with LOOKUPS only, not case or if because of 100's of values in lookup1 and lookup2.
Events:
20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=SET COUNT=0 STATUS=P REASON=ABC1
20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1235 TYPE=SET COUNT=1 STATUS=P REASON=abc1
20140122T100516 EMP MESSAGE=RES COUNTRY=USA ACCNO=1236 TYPE=SET COUNT=0 STATUS=P REASON=def1
20140122T100517 EMP MESSAGE=RES COUNTRY=USA ACCNO=1237 TYPE=SET COUNT=1 STATUS=P REASON=DEF1
20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1238 TYPE=SET COUNT=0 STATUS=P REASON=ghi1
20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1239 TYPE=SET COUNT=1 STATUS=P REASON=KLM1
20140122T100516 EMP MESSAGE=RES COUNTRY=USA ACCNO=1240 TYPE=SET COUNT=0 STATUS=P REASON=OPQ1
20140122T100517 EMP MESSAGE=RES COUNTRY=USA ACCNO=1241 TYPE=SET COUNT=1 STATUS=P REASON=xyz1
lookup1.csv
abc1
def1
ghi1
xyz1
lookup2.csv
ABC1
DEF1
KLM1
OPQ1
Expected output in a table:
ACCNO TYPE STATUS REASON BOX
1234 SET P ABC BOX2
1235 SET P abc BOX1
1236 SET P def BOX1
1237 SET P DEF BOX2
1238 SET P ghi BOX1
1239 SET P KLM BOX2
1240 SET P OPQ BOX2
1241 SET P xyz BOX1
Thanks in advance.
-P
Try like this
your base search | lookup lookup1.csv REASON OUTPUTNEW REASON as BOX1 | lookup lookup2.csv REASON OUTPUTNEW REASON as BOX2 | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") | fields - BOX1 BOX2
Try like this
your base search | lookup lookup1.csv REASON OUTPUTNEW REASON as BOX1 | lookup lookup2.csv REASON OUTPUTNEW REASON as BOX2 | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") | fields - BOX1 BOX2
@somesoni2
Thanks Somesh.
Hi @somesoni2
If i use this query if any REASON comes apart from lookup1 and lookup2 by default it is taking BOX1.In this case it should show "N/A"
for example: If i get this new event with different REASON as shown below.
20150126T100519 EMP MESSAGE=RES COUNTRY=USA ACCNO=1290 TYPE=SET COUNT=1 STATUS=P REASON= ASIA
By defalut it is going to BOX1. It should display N/A
Try this:
| eval BOX=if(isnull(BOX1),if(isnull(BOX2),"N/A","BOX2"),"BOX1")
@Raschko
It is showing all "N/A" only no luck
Have you put the following after the eval?
| fields - BOX1 BOX2
Replace | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2")
with | eval BOX=coalesce(BOX1,BOX2,"N/A")
Any suggestion/idea experts?