Merge multipe lookup files

[rdownie]

I am trying to merge 3 lookup files having them join on one field but keep all additional fields and records(that don't join) in the output. I have tried join type=outer but that appears to only keep all records from one side of the join.

File 1:

Hostname, IP, MAC

A,192.168.1.1,MACC

D,192.168.1.2,MACF

G,192.168.1.3,MACI

File 2:
Hostname, Owner, Location

A,John,Boston

J,Tom,NewYork

M,Fred,Cleveland

File 3:

Hostname,Switch,Port

A,B0K-mdfa,FA/02

J,B0K-mdfa,FA/03

X,B1K-mdfb,FA/03

I want the results to have:

Hostname,IP,MAC,Owner,Location,Switch,Port

A,192.168.1.1,MACC,John,Boston,B0K-mdfa,FA/02

D,192.168.1.2,MACF,,,,,

G,192.168.1.3,MACI,,,,,

J,,,Tom,NewYork,B0K-mdfa,FA/03

M,,,Fred,Cleveland,,,

X,,,,,B1K-mdfb,FA

Basically, this is what I have been trying:

| inputlookup File1 | join type=outer Name [|inputlookup File2 ] | join type=outer Name [|inputlookup File3 ]

What happens is what I would expect, I get all records from file 1, those that join to it from File 2 and then those that join to that from file 3 when I want all records from all 3 files basically merged on Name. I tried using append but that won't work either. Any ideas?

Thanks,
-Bob

[somesoni2]

Extending answer from Ayn ->

|inputlookup File1 | fields Hostname |
append [|inputlookup File2 | fields
Hostname] | append [|inputlookup File3
Hostname] | dedup Hostname | join
type=outer Hostname [|inputlookup
File1] | join type=outer Hostname
[|inputlookup File2]| join type=outer
Hostname [|inputlookup File3]

[rdownie]

I thought using append and then piping it to transaction Name but for some reason that doesn't return any results?

[Ayn · Accepted solution]

Why not just use the lookup files the way they're intended to be used, as lookups?

|inputlookup File1 | lookup File2 Hostname | lookup File3 Name

EDIT: Updated version which gets all the Hostnames to begin with:

|inputlookup File1 | fields Hostname | append [|inputlookup File2 | fields Hostname] | append [|inputlookup File3 Hostname] | dedup Hostname | lookup File1 Hostname | lookup File2 Hostname | lookup File3 Hostname

[rdownie]

I was just taking that approach.
Thanks!!!
-Bob

[Ayn]

Fair enough - updated my answer with an approach that will grab all the hostnames first of all.

[rdownie]

That is only going to give me the records that match from file 1 in file 2 and file 3. I need the records from all 3 files merged, regardless if they are found in the other files.