How to join two tables with default row if tables do not match?

[harshal_chakran]

Hi,

i have a table whose result is as below:

parameter value result
P1          V1    R1
P2          V2    R2
P3          V3    R3
P4          V4    R4
P5          V5    R5

And a lookup file as below:

parameter value result color1 color2
P1          V1    R1     C1     C2
def        def   def     C3     C4

How can I join the two table? If the parameter value result doesn't match for both, then it should take the "def" row value.

Planning to get the result as below:

parameter value result color1 color2
P1          V1    R1     C1     C2
P2          V2    R2     C3     C4
P3          V3    R3     C3     C4
P4          V4    R4     C3     C4
P5          V5    R5     C3     C4

I have used the join , but doesnt displays the result as I expected.
Please Help...!!

[sideview · Accepted solution]

Let's say your lookup is named "myLookup", then here's what you want. The rows that are a match for all three values, (parameter, value and result) will get the corresponding color1 and color2 values from the lookup. The rows that are not a match will end up with whatever color1 and color2 values are listed in the row that has the value "def" for all three fields.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 |fillnull defaultParameter defaultValue defaultResult value="def" | lookup myLookup parameter as defaultParameter value as defaultValue result as defaultResult OUTPUT defaultColor1 defaultColor2 | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

And I don't normally advise using append or join, but in cases where the subsearch is extremely efficient and returns only a couple rows, their drawbacks don't really come into play.

Since the above example runs the lookup twice, and with a join command all you're joining in is an inputlookup search yielding only a single row, the join command way below is a viable alternative.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | rename color1 as defaultColor1 defaultColor2 | eval foo=1 | table foo defaultColor1 defaultColor2] | fields - foo | eval color1=coalesce(color1,defaultColor1) | eval color2=coalesce(color2,defaultColor2)

Or instead of the eval+coalesce at the end you can avail yourself of the join command's overwrite argument.

<your search terms> | lookup myLookup parameter value result OUTPUT color1 color2 | eval foo=1 | join overwrite=f foo [| inputlookup myLookup | search parameter="def" value="def" result="def" | eval foo=1 | table foo color1 color2] | fields - foo 

[woodcock]

This will do it:

... lookup <yourLookupName> parameter value result OUTPUT color1 AS color1lookup color2 AS color2lookup | eval color1=coalesce(color1lookup,"C3") | eval color2=coalesce(color2lookup,"C4")