Help on top10 search with variations

[christian_l]

Hi community,

let's say we have a online shop which is selling products which could appear in different variations - colors for example. So we have the fields productID and variation.
Now I'd like to have a table showing the top10 sold productID, with all variations in the same row and their count.

productID    count   variation
-----------------------------------------------------
shirt1       532     blue (232)
                     red (300)
-----------------------------------------------------
jeans1       47      blue (35)
                     black (12)
-----------------------------------------------------
....

... and so on. The number behind the variation value is the count for this variation. The complete sorting of the list should be based on the count field so I get the top10 independent from the variation.
I already tried to add the variation field to the top command which results in a different sorting-order when comparing with the top command for productID.

Is there a solution for the problem I'm facing?

Regards,
Christian

[christian_l]

Maybe it would be easier to have the variations as columns and not inside one row.

For example like this:

productID count variation1 variation2 ...

---

product1 501 81 420
product2 38 0 38
...

[wpreston]

EDIT: Reply to comment. I can't reply in comments for some reason, the reply will not post so my reply is posted here

Hmm, that's interesting... When run on my data using correlating field relationships (ie every productID has one or more variations), each variation adds up to the total count of productID. Do you have any productID's that do not have a variation? Or is it possible that the variation field might not be extracted in some of your events? You can find out with this search:

productID=* NOT variation=*

Also, can you post your full search, including everything prior to the stats command?

---

Original answer:
I'm sure there are more elegant ways of doing this, but here is my solution. Using the join command gets you most of the way there. Something along these lines:

...your search 
| fields productID 
|stats count as ProductIDCount by productID 
| join productid max=0 [search ...your search...
    | fields productID variation 
    | stats count as VariationCount by productID variation]

This gets you a table with the product, product count, variation, and variation count, but it lists the product and product count on each line instead of only once like your request shows. To get it to display only once, while showing all of the variations and their counts, you need to play with multivalue fields, then to reduce it to only the top 10 results you use sort and head. Add this to the search:

| eval VariationCombined=variation." (".VariationCount.")"
| makemv VariationCombined
| table productid ProductIDCount VariationCombined
| mvcombine delim="," VariationCombined
| table productID ProductIDCount VariationCombined
| sort -ProductIDCount 
| head 10

So the complete search looks like this:

...your search 
| fields productID 
|stats count as ProductIDCount by productID 
| join productid max=0 [search ...your search...
    | fields productID variation 
    | stats count as VariationCount by productID variation]
| eval VariationCombined=variation." (".VariationCount.")"
| makemv VariationCombined
| table productID ProductIDCount VariationCombined
| mvcombine delim="," VariationCombined
| table productID ProductIDCount VariationCombined
| sort -ProductIDCount 
| head 10

[christian_l]

Thank you wpreston for your help, but your search didn't solve my problem.
With the search query above I got different counts for ProductIDCount and VariationCount. I can see this clearly on one product which has only one variation. Product count and variation count for this product differ.

productID count variation

---

product1 546 red(352)

[christian_l]

Hey davecroto,

the core search, which should generate the table looks like this:

stats count by productID, variaton | fields - percent | sort -count limit=10

Regards,
Christian

[davecroto]

Can you post your search?