How to use lookup to evaluate thresholds?

[manmeet99]

Have been trying to crack this for a long time. Would highly appreciate any help.

I have a lookup similar to this:

Merchant_ID Amount_threshold Count_threshold Description
Merchant1 15 1 ABC
Merchant2 11 5 XYZ
Merchant3 25 5 LMN
* 13 1 all_other_merchants

And my events are:

Merchant_ID Amount
Merchant1 10
Merchant1 20
Merchant2 10
Merchant3 40
Merchant7 30
Merchant7 20

I want my query to return something like this:

If for each merchant:

Total_amount>amount_threshold AND count>count_threshold

Then show it in the table below

Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold
Merchant1 ABC 30 2 15 1
Merchant7 all_other_merchants 50 2 13 1

Update: Added a new case - a wild card merchant ID that captures all merchants not explicitly specified in the lookup and applies thresholds to that merchant.

[woodcock]

Like this

Your Base Search Here | appendpipe [|inputlookup YourLookupHere]
| stats values(*) AS * sum(Amount) AS Total_amount count AS Total_count BY Merchant_ID
| where Total_amount>Amount_threshold AND Total_count>Count_threshold
| table Merchant_ID Description Total_amount Total_count Amount_threshold Count_threshold

[manmeet99]

Did not work out for me. Trying to troubleshoot.

[woodcock]

I tested and it works. It may be that you have not described something in your dataset (probably a field name) accurately? Double-check all the fieldnames and make sure that they match.

[DalJeanis]

Use a left join against the lookup table, like this...

(your event search) 
| stats count as Total_count sum(Amount) as Total_amount by Merchant_ID
| join type=left Merchant_ID
    [| inputlookup yourlookup.csv | table   Merchant_ID Amount_threshold Count_threshold Description ]
| where (Total_amount>Amount_threshold AND Total_count>Count_threshold) OR (isnull(Amount_threshold))
| fillnull value="((Not Found))" Description Amount_threshold Count_threshold
| table Merchant_ID  Description Total_amount Total_count Amount_threshold Count_threshold

...and here's some run-anywhere code to show you that it works...

 | makeresults 
 | eval mydata="Merchant1,10 Merchant1,20 Merchant2,10 Merchant3,40 Merchant4,40" 
 | makemv mydata 
 | mvexpand mydata 
 | rex field=mydata "(?<Merchant_ID>[^,]+),(?<Amount>.*)"
 | stats count as Total_count sum(Amount) as Total_amount by Merchant_ID
 | join type=left Merchant_ID
     [| makeresults 
      | eval mylookup="Merchant1,15,1,ABC Merchant2,11,5,XYZ Merchant3,25,5,LMN" 
      | makemv mylookup 
      | mvexpand mylookup 
      | rex field=mylookup "(?<Merchant_ID>[^,]+),(?<Amount_threshold>[^,]+),(?<Count_threshold>[^,]+),(?<Description>.*)" 
      | table Merchant_ID Amount_threshold Count_threshold Description ]
 | where (Total_amount>Amount_threshold AND Total_count>Count_threshold) OR (isnull(Amount_threshold))
 | fillnull value="((Not Found))" Description Amount_threshold Count_threshold
 | table Merchant_ID  Description Total_amount Total_count Amount_threshold Count_threshold

[niketn]

Assuming the lookup file name is merchantlookup.csv

<YourBaseSearch> 
| stats count as Total_count sum(Amount) as Total_amount by Merchant_ID 
| lookup merchantlookup MerchantID OUTPUT Amount_threshold Count_threshold Description
| where Total_amount>Amount_threshold AND Total_count >Count_threshold
| table MerchantID Description Total_amount Total_count Amount_threshold Count_threshold

[manmeet99]

I tried this out. The threshold values display just one value for all rows instead of displaying threshold value applicable to the specific merchant.