I'm trying to write a query that shows the first and last time an item was sold, and if that item was ever sold to a particular customer (among other customers).
For example, the output would be
ItemName FirstBought LastBought Sold to John?
ItemA 10-1-2019 21-4-2019 No
ItemB 3-5-2017 21-1-2020 Yes
ItemC 6-12-2018 2-10-2019 No
My query is
index=db | eval soldToJohn=if(customer="John","Yes","No") | stats earliest(_time) as firstBought latest(_time) as lastBought by itemName soldToJohn | table itemName firstBought lastBought soldToJohn
My problem is by doing the stats ... by soldToJohn
, I'm now getting 2 lines for ItemB
, one with soldToJohn="Yes"
, the other with soldToJohn="No"
. But if I leave out soldToJohn
in the stats
part, then the soldToJohn
column is empty. How do I have just 1 result with soldToJohn="Yes"
, as long as one of the customers is John?
Hi @wu_weidong,
did you tried something like this?
index=db
| eval soldToJohn=if(customer="John","Yes","No")
| stats earliest(_time) as firstBought latest(_time) as lastBought values(soldToJohn) AS soldToJohn BY itemName
| table itemName firstBought lastBought soldToJohn
Ciao.
Giuseppe
Hi @wu_weidong,
did you tried something like this?
index=db
| eval soldToJohn=if(customer="John","Yes","No")
| stats earliest(_time) as firstBought latest(_time) as lastBought values(soldToJohn) AS soldToJohn BY itemName
| table itemName firstBought lastBought soldToJohn
Ciao.
Giuseppe
The lines for ItemB are combined into 1 line, but the soldToJohn
column contains both "No" and "Yes". Is there a way to display "Yes" if it is present (even if there is a "No" too), and "No" if there is no "Yes"?
ItemName FirstBought LastBought Sold to John?
ItemA 10-1-2019 21-4-2019 No
ItemB 3-5-2017 21-1-2020 No
Yes
ItemC 6-12-2018 2-10-2019 No
I've added a eval soldToJohn_flag = if(in(soldToJohn, "Yes"),"Yes","No")
to display those lines with both "No" and "Yes" to "Yes", and those with "No" only to "No". This effectively gives me what I'm looking for.