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...!!
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
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
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")