How to use two lookups for comparison

[mprreddy51]

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

[somesoni2 · Accepted solution]

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

[mprreddy51]

@somesoni2

Thanks Somesh.

[mprreddy51]

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

[Raschko]

Try this:

| eval BOX=if(isnull(BOX1),if(isnull(BOX2),"N/A","BOX2"),"BOX1")

[mprreddy51]

@Raschko

It is showing all "N/A" only no luck

[Raschko]

Have you put the following after the eval?

| fields - BOX1 BOX2

[somesoni2]

Replace | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") with | eval BOX=coalesce(BOX1,BOX2,"N/A")

[mprreddy51]

Any suggestion/idea experts?