Compare value in each row from column B with all values in column A

[seva98]

Hi, it probably very simple problem but looks like I am using wrong queries on Google and can't find a solution. I need to compare value in each row with all the value from the different column.

For example:

Col A    Col B
1        2
2        14
3        -
4        abc
5        a
6        6
7        c
8        33
9        -

And leave only rows where value exists in Col A or is set to "-", so the result in this case will be:

Col A    Col B
1        2
3        -
6        6
9        -

[dmarling]

I'm failing to understand how 3 and 9 in column A would return since there is no 3 or 9 value in column B if you goal is to only return results in A that exist in B. Based on your requirements only COLA=1 and COLB=6 would return, unless you want an OR statement to also include any hyphens in COL B in addition to your initial requirements. If that's the case, this will accomplish that. You are essentially making a recursive query where you limit the results of Col B by what's in Col A first with this query:

| makeresults count=1 
        | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
        | fields - _time 
        | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
        | eval data=trim(data) 
        | mvexpand data 
        | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
        | fields - data 
        | eval search="ColB=\"".ColA."\"" 
        | stats values(search) as search 
        | eval search="(".mvjoin(search, " OR ").")"

This returns results that look like this (ColB="1" OR ColB="2" OR ColB="3" OR ColB="4" OR ColB="5" OR ColB="6" OR ColB="7" OR ColB="8" OR ColB="9")

You then take your main query and put a sub query after a where clause to limit your results. You can add that OR statement to grab the hyphenated Col B values on this:

| makeresults count=1 
    | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
    | fields - _time 
    | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
    | eval data=trim(data) 
    | mvexpand data 
    | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
    | fields - data 
    | where 
        [| makeresults count=1 
        | eval data="1        2
 2        14
 3        -
 4        abc
 5        a
 6        6
 7        c
 8        33
 9        -" 
        | fields - _time 
        | rex field=data max_match=0 "(?<data>[^\n\e]+)" 
        | eval data=trim(data) 
        | mvexpand data 
        | rex field=data "(?<ColA>[^\s]+)\s+(?<ColB>[^\s]+)" 
        | fields - data 
        | eval search="ColB=\"".ColA."\"" 
        | stats values(search) as search 
        | eval search="(".mvjoin(search, " OR ").")"] OR ColB="-"

Your results will then look like this:

ColA    ColB
1        2
3        -
6        6
9        -

[Sukisen1981]

not clear at least to me.
why is 2 , 14 not expected output? Can you elaborate more on the needed output?

[seva98]

Because it gets value from row in Col B and checks it against all values in Col A. 14 is not in Col A.

[Sukisen1981]

then 6,6 should come since 6 exists in column A and not 6,14? Since 14 does not exist in column A?
I am sorry, I am unable to understand the requirement

[seva98]

Ah, you are right, that was typo. I corrected the example