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.
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
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
hi peterkn
can you send your xml code ?