Joining 3 sets of results from 2 indexes to graph

[peterkn]

Say in table A I have just 1 column result:

Animal:
Chicken 
Chicken
Cow
Cat
Cow
Cow
Dog

My query of "stats count by Animal" Would return me with:

Chicken 2
Cow 3
Cat 1
Dog 1

Now I have table B

Animal  Name  Age:
Cat Mick 3
Cat 
Dog Mike 1
Dog Moose 

I would like to join the results to make a multi-column bar graph (or a stacked bar graph) with the following result (Animal,Animal Count, Has_name, Has Age)

Cow 3 X Y, Cat 1 X Y, Dog 1 X Y

where X is the number of the respective animal instance that appear in table B with a Name specified. Y is with an Age specified
So the final table would be

Cow 3 0 0, Cat 1 1 1, Dog 1 2 1

and as a result, the multi-column graph would have 3 grouped columns of Cow, Cat and Dog.

Any suggestion/help is greatly appreciated.

[woodcock · Accepted solution]

Like this (adjust to find your way of accessing the data, instead of faking it like I did):

|noop|stats count AS Animal|eval Animal="Chicken,Chicken,Cow,Cat,Cow,Cow,Dog"|makemv delim="," Animal | mvexpand Animal | stats count BY Animal
| appendpipe [|noop|stats count AS Animal|eval Animal="1,2,3,4"|makemv delim="," Animal | mvexpand Animal
   | eval Name=case(Animal=1, "Mick", Animal=3, "Mike", Animal=4, "Moose")
   | eval Age=case(Animal=1, "3", Animal=3, "1")
   | eval Animal=case(Animal<3, "Cat", true(), "Dog") | stats count(*) AS * BY Animal]
| stats values(*) AS * BY Animal | fields Animal count Name Age | rename Age AS withAges Name AS withNames

[chimell]

hi peterkn
can you send your xml code ?