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.
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.
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 ?
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 |