Archive

How do I join multiple lookup tables?

New Member

I have couple of lookup tables as follows:
Table 1
A 1
B 5
C 6

Table 2
A one
A two
A three
B one
C one

Trying to lookup so that all the values from Table 1 that are IN table 2 are returned, but I'm only getting the 1st entries. My lookup returns
A 1 one
B 5 one
C 6 one

But I want
A 1 one
A 1 two
A 1 three
B 5 one
C 6 one

Here is the lookup. Appreciate any help.

| inputlookup table 1
| join type=inner [ inputlookup table 2]

Tags (1)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

Try this...

| inputlookup table 1 
| join type=inner max=0 [ inputlookup table 2]

There is also a way for an admin to officially set up the second lookup to return multiple results, but the above will work.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Try this...

| inputlookup table 1 
| join type=inner max=0 [ inputlookup table 2]

There is also a way for an admin to officially set up the second lookup to return multiple results, but the above will work.

View solution in original post

0 Karma

New Member

Fantastic. Exactly what I was looking for 🙂

0 Karma

New Member

Follow up question. How can I continue this lookup against a third table but return the results ONLY if they are not present in the third table as follows:

Result of join of first to table
A 1 one
A 1 two
A 1 three
B 5 one
C 6 one

3rd table
B Done
C Done

Return
A 1 one
A 1 two
A 1 three

Thanks

0 Karma

SplunkTrust
SplunkTrust

@abidgoliwb - this forum does not work well with "followup questions" that add new items after the question is solved - you will end up waiting for an original answerer who may not log on for days.

It's best to write up the new question, with all the information needed to understand what is being asked, and include a reference to the prior answer.

I believe you are looking for

| inputlookup table1 
| join type=inner max=0 keyfield [ inputlookup table2]
| lookup table3 keyfield OUTPUT keyfield AS foundit
| where isnull(foundit)
0 Karma