joining tables miss some values

[smolcj]

hi,
i have 2 tables to join and when i am using outer join, i am able t join 2 tables but not able to join all the values from the second table if some of he values are missing

table 1

< no value

    1   we

    2   this

    3   that

    4   from

table 2

value some1 some2 some3

we     ddf   df   dfdf

this   sd     sf   sdfsd

that   sds    sds   sdfds

from                here 



when i am trying to join these two tables wth

 `......|join type=outer value [search source=file.txt|transaction threadname|table value1 some1 some2 some3]

`

here all the values are mapping finely but not the last row as there is empty fileds in that...
how to resolve this?
please help
thankyou

[qk9962549]

join max=0

[jrodriguezap]

Hi there
Someone could complete what smolcj mentioned? I have the same problem with null fields.
In your example, the result of the table is as follows:

no value some1 some2 some3
1  we     ddf   df   dfdf
2  this   sd    sf   sdfsd
3  that   sds   sds  sdfds
4  from                
4                      here

Help me please!

[troywollenslege]

One thing to be careful is that you remember that join is case sensitive (for some reason)..

It doesn't look like this is the case in your example data, but something to keep in mind. Check out this posting.

http://answers.splunk.com/answers/47699/case-insensitive-join

[smolcj]

cramasta,, fillnull is not providing any change to the result..
exact search is

 index=main source=SUCCESS | stats values(a) by b| mvexpand values(a)| makemv delim="," values(a) | mvexpand values(a)  |rename values(a) as a|join type=outer a[search  index=main source=SUCCESS  |transaction startswith=WRT_1023 endswith=WRT_8233 |fillnull|table a c d  ]

[cramasta]

what happens if you use fillnull?

`......|join type=outer value [search source=file.txt|transaction threadname| fillnull | table value1 some1 some2 some3]

[martin_mueller]

Works for me using this as an example:

| gentimes start=-1 increment=5m | head 1 | eval no = "1,2,3,4" | makemv no delim="," | mvexpand no | eval value = case(no==1,"v1",no==2,"v2",no==3,"v3",no==4,"v4") | table no value | join type=outer value [gentimes start=-1 increment=5m | head 1 | eval value = "v1,v2,v3,v4" | makemv value delim="," | mvexpand value | table value | eval some3 = replace(value,"v","s3_") | eval some2 = case(value=="v1","s2_1",value=="v2","s2_2",value=="v3","s2_3") | eval some1 = case(value=="v1","s1_1",value=="v2","s1_2",value=="v3","s1_3")]