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 -
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 -
not clear at least to me.
why is 2 , 14 not expected output? Can you elaborate more on the needed output?
Because it gets value from row in Col B and checks it against all values in Col A. 14 is not in Col A.
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
Ah, you are right, that was typo. I corrected the example