Merging two columns as full join using append

[Kadae]

Hi, 
I have the results of an append operation as follows:

ID	Col3	col4	col5
a			abc
a	abc	No
a	xyz	Yes
b			abc
b			xyz
b	xyz	No
b	fgh	Yes
b	abc	No
f			abc
f	abc	No
f	xyz	No
i			abc
i			xyz
i	xyz	Yes
i	abc	No

The result from the first table and the result from the second should be merged respectively.

I cannot use | stats values(col1) values(col2) values(col3) by ID because I cannot lose the distinction between "No" and "Yes" for Col3.

I want to create a result as follows:

ID	Col3	col4	col5
a	abc	No	abc
a	xyz	Yes
b	xyz	No	xyz
b	fgh	Yes
b	abc	No	abc
f	abc	No	abc
f	xyz	No
i	xyz	Yes	xyz
i	abc	No	abc

I think something like SQL's full join would do the trick, but I am totally stuck.

[yuanliu]

One of Splunk's biggest taboo is join.  SQL is designed to make join efficient.  But Splunk is NoSQL.  If you feel there is a need for SQL like join, it is usually because the search strategy is wrong.

It is much better if you describe your dataset and the search used to obtain those two tables, and describe the desired output.  There is usually a more Splunk way to get the result and avoid join.

[andrew_nelson]

Is the goal to have Col5 appear in the row where its value is an exact match to Col3 ? Or is your last two rows in the output actually correct ? 

[Kadae]

Yes, column 3 should equal column 5.

*** The last row contained a mistake, which I have corrected. Abc should always equal abc
That's how the clean tables look:

ID	col5
a	abc
b	abc
b	xyz
f	abc
i	abc
i	xyz

ID	Col3	col4
a	abc	No
a	xyz	Yes
b	xyz	No
b	fgh	Yes
b	abc	No
f	abc	No
f	xyz	No
i	xyz	Yes
i	abc	No