How to write this query when variable names instead of constants need to be used to automate the query

[iTechEvent]

Here is a simplified version of my issue.

I have csv file as below named Q.csv

1. Q1avg, Q2avg
2. 100 ,
3. , 90
4. , 100
5. , 110

Need to check how many times Q2avg values exceeded Q1avg value.
The constraint, I cant use hard coded values in the where, eval statements.
For eg. | inputcsv Q.csv | where Q2avg > 100

instead of 100 i need to use something like first(Q1Avg) i.e. instead of hard coded constant need to use a variable name for constant.

| inputcsv Q.csv | where Q2avg > first(Q1avg) but it wont work

Also

| inputcsv Q.csv | stats count(eval(Q2avg > 100 )) -- cant use this hard coded value of 100.

Instead need to use something like

| inputcsv Q.csv | stats count(eval(Q2avg > Q1avg ) ) -- not 100 but use field/variable name for it but it wont work

The reason is I need to automate this and not use manual input.

Any help will be appreciated.

[martin_mueller]

How 'bout this?

| inputcsv Q.csv | eventstats first(Q1avg) as myQ1 | where myQ1 < Q2avg

[linu1988 · Accepted solution]

Hello,
You can use map command.
1st:

| inputcsv Q.csv | stats first(Q1avg) as AvgQ1)|map[| inputcsv Q.csv | where Q2avg > $AvgQ1$]

2nd:

| inputcsv Q.csv | stats first(Q1avg) as AvgQ1|map [| inputcsv Q.csv | stats count(eval(Q2avg > $AvgQ1$ ))]

Thanks