How do you compare values in two columns created using two different searches and then keep only uncommon values in a separate table(field)?

[ashikuma]

I want search to check values one by one from "table A" and parallel match with values in "table B" . If any value from "table A" matches with any "table B" values, then I need those value to be in a third table(field).

OrderNumberFailureA OrderNumberFailureB
353649273                                  353648649
353649184                                  353648566
353649091                                  353616829
353649033                                  353648649
353648797    
353648680    
353648745    
353648730    
353638941    
353649331    
340568517    
353638941    
353648361    
349156251    
353649335    
353649091    
353649240    
353649143    
353649160    
353649092    
353649312    
353648984    
353649091    
353649163    
353649240    
353649092    
353649143    
353649095    
353649008    
353648984    
353649008    
353648794    
353648856    
353649273    
353648796    
353648754    
353648620    
353648594    
353648794    
353648649    
353648685    
353648651    
353638941    
353648610    
353649273    
353649241    
353649163    
353616829    
353649163    
353648754    
353649347    
353649335    
353648748    
353648661    
353648649    
353648754    
353648649    
353648649    
353648984    
353648994    
353648802    
353649263    
353648649    
353649347    
353649240    
353649178    
353616829    
353649092    
353648984    
353648754    
353648768    
353648749    
353649387    
353648680    
353648649    
353648566

[jlelli · Accepted solution]

If all the values are uniques (and they should be if they are Orders) you can try something like:

| foreach  OrderNumberFailureA [eval match=if(OrderNumberFailureA=OrderNumberFailureB,  OrderNumberFailureA, NULL)] | table  OrderNumberFailureA OrderNumberFailureb match

I don't understand why all the table commands (since there is not direct row correlation between the three fields) in this case a stats would be faster:

search 1 | rename OrderNumberFailure AS OrderNumberFailureA |append [search search2 |rename OrderNumberFailure AS OrderNumberFailureB] | foreach  OrderNumberFailureA [eval match=if(OrderNumberFailureA=OrderNumberFailureB,  OrderNumberFailureA, NULL)] | stats  values(OrderNumberFailureA) values(OrderNumberFailureB) values(match)

[ashikuma]

above mentioned both queries are not working , I can't see anything in third table.

[493669]

If I understand correctly , you need to find if OrderNumberFailureA value matches with OrderNumberFailureB then you want value in another column,
then please try-

|eval c=if(OrderNumberFailureA=OrderNumberFailureB,OrderNumberFailureA ,NULL)

try this sample run anywhere example-

| makeresults |eval a=12, b=12
|append[|makeresults|eval a=23, b=98]|eval c=if(a=b,a,NULL)

[architkhanna]

This works fine when there is no repetition in the multivalued field.It fails such scenraio.
Any suggestions in this regard?

[ashikuma]

above examples is not working in my case. I want unique values in separate table after comparing two tables.
I have two search queries, from where I am getting two tables (values which I pasted above) , after creation of table I want query which match each value from table A with each values of table B not only corresponding value to that field. Example - if value 353649273 is first value from first table then it should match with all 4 values in table B not value corresponding to that.

Below is my query which is creating two tables :

search 1 | table OrderNumberFailure | rename OrderNumberFailure AS OrderNumberFailureA |appendcols [search search2 | table OrderNumberFailure | rename OrderNumberFailure AS OrderNumberFailureB]

I need query after that which compares values in both tables like foreach loop and give me final table which contains only unique values from both tables.

Waiting for your comments

[pgadhari]

did you resolve this issue ? can you tell how you did that query ?

[ashikuma]

Can anyone have a look at this issue