Join if one of two fields match a lookup

[ChrisCLewis]

Hi there, many thanks for reading this far and for any insights you can give.

I have a base search which returns a number of fields including "Website" and "IP address"

In addition, there is a lookup table that has two fields:
Address (with either a website or IP address)
Date (which strangely has a date).

What I would like to do is add the Date to records where the Address is like either "Website" OR "IP address"

(e.g. Address could contain BBC to cover bbc.co.uk, bbc.com, bbc.co.uk/news, bbc.com/sports etc.).

If there is another way to avoid using join at all that would be a bonus

My search is currently along line of the following but it returns 0 results:

|base search |table Website "IP address"
|join type=left 
[inputlookup input.csv |fields Address Date |eval Website= Address |eval "IP address"= Address]
|table Website "IP address" Date

I have tried putting fields names on the Join part and renaming Address, but this only joins for one field not either.

Chris

[maciep · Accepted solution]

what about maybe just using the lookup command...maybe something like this?

| base search 
|table Website "IP address"
| lookup input.csv Address as Website OUTPUT Date
| lookup input.csv Address as 'IP address' OUTPUTNEW Date
| table Website "IP address" Date

[ChrisCLewis]

Good afternoon,
Thank you for your answer

It is working perfectly where there is an exact match. The trouble is is for entries such as "bbc" (which are needed to cover multiple sites bbc.co.uk and bbc.com).

At present the fields in the lookup are not set for wildcard use so can't use "bbc", and not sure if I can get the "holders of the keys" to make the changes

[ChrisCLewis]

Got the wildcard sorted and working correctly now.

[maciep]

yeah, if you can't get the wildcard option for the lookup, this problem does become a bit different. Would the wildcard match always be the domain prefix? If so, then you could maybe create a field for that and add another lookup. Maybe like this?

| base search 
| table Website "IP address"
| eval Domain  = mvindex(split(Website,"."),0)
| lookup input.csv Address as Website OUTPUT Date
| lookup input.csv Address as Domain OUTPUTNEW Date
| lookup input.csv Address as 'IP address' OUTPUTNEW Date
| table Website "IP address" Date