How to show the first and last time an item was sold, and if it was ever sold to a particular customer (among other customers)?

[wu_weidong]

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?

[gcusello · Accepted solution]

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

[wu_weidong]

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

[wu_weidong]

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.