Hi, I wonder whether someone may be able to help me please.
I'm using the following query to successfully return a list of telephone numbers.
`field_wmf(misReceived)`
| fields detail.45BxData
| rex field=detail.Data "dayPhoneNumber\"\:\"(?<day>[^\"]+)\""
| rex field=detail.Data "eveningPhoneNumber\"\:\"(?<evening>[^\"]+)\""
| fillnull value="" day evening
| eval telno=day + ";" + evening
| makemv delim=";" telno
| mvexpand telno
| search telno="07*"
| rex mode=sed field=telno "s/ //g"]
| dedup telno
I then have a lookup table of telephone numbers which I want to compare against my query.
Both field names are called tel no, and what I'd like to do is create another column called "Match" which will indicate a match between the query and lookup file by virtue of the letter "Y" but I'm not quite sure about how to go about this.
I just wondered whether someone could look at this please and offer some guidance on how I can go about about this.
Many thanks and kind regards
Chris
All,
I was able to resolve this with the following:
| lookup tellist.csv telno OUTPUT telno as Matched
| eval Matched=if(isnotnull(Matched), "Y", "N")
| table telno Matched
Reading this, I'd probably go with an append to add in your lookup table, to add the entire table to the bottom.
something like this maybe?
`field_wmf(misReceived)`
| fields detail.45BxData
| rex field=detail.Data "dayPhoneNumber\"\:\"(?<day>[^\"]+)\""
| rex field=detail.Data "eveningPhoneNumber\"\:\"(?<evening>[^\"]+)\""
| fillnull value="" day evening
| eval telno=day + ";" + evening
| makemv delim=";" telno
| mvexpand telno
| search telno="07*"
| rex mode=sed field=telno "s/ //g"]
| dedup telno
|append [inputlookup telno.csv|eval telno1=telno]
|stats values(*) as * by telno
|eval Match=if(isnotnull(telno1),"Y",null())
Hi @cmerriman, thank you for coming back to me with this, but unfortunately it didn't work because it failed to create a filed called "Match", but I have managed to resolve this wuth:
| lookup tellist.csv telno OUTPUT telno as Match
| eval Match=if(isnotnull(Match), "Y", "N")
| table telno Match
Many thanks and kind regards