index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category by department limit=0
| transpose 0 header_field=sports_category
| addtotals fieldname=TOTAL
| rename column as "Department"
| fillnull value="-"
My example code looks like that. The table would probably look like this:
Department | BASKETBALL | GOLF | SWIMMING | TOTAL
High School | 123,123,456 | 123,123 | 123,123,432 | total of this
Elementary
College
Masters
What I want is to add another column for "Coach" for each department (I've already set up the automatic lookup for this). So the columns would be:
Department, Basketball, Golf, Swimming, TOTAL, Coach.
I tried:
-| fields + Coach
-| table Department, Basketball, Golf, Swimming, TOTAL, Coach // this works but the Coach column has no value.
Please help.
EDIT: The original search is fine. The coach is already in the fields list, I just want to call it as another column. Thank you!
I used:
index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category by department limit=0
| transpose 0 header_field=sports_category
| addtotals fieldname=TOTAL
| rename column as "Department"
| appendcols [ search index=monthly_budget
| stats values(Coach) as "Coach" by department
| fields - department]
| fillnull value="-"
I know this isn't the best way to do this but it works for me. hehe It'll be really cool if there's another good way to do this. Thank you!
solved it by appendcols! Thank you for your response. 😃
hi @rajyah
I'm glad you found a solution to your problem. Would you add more detail as to how you came to fix your table? Other users would love to hear your detailed explanation! Also, can you please approve this answer so others know where to look for the correct solution?
Thanks!
Here the field is only sports_category ,department,sum(TOTAL_BUDGET) .
index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category by department limit=0
For example, how about doing this?
| stats sum(TOTAL_BUDGET) by department,sports_category, Coach
Tried stats command sir but I didn't get the result I'm looking for. Chart command is the closest hint for me.
Is Coach's field in monthly_budget?
Coach and the Department fields are created by the automatic lookup. And when I check the fields after running my original search, I can see the field in the fields list. The original search is okay but the thing is I can't call the Coach field.
By the way, the department and the coach field are in the lookup table but I've already set it up using automatic lookup.
Thank you for your response sir.
@rajyah ,
If you have a lookup already for Coach per department, assuming it as entries as
Department,Coach
High School ,CoachA
Elementary,CoachB
Try,
index=monthly_budget
| chart sum(TOTAL_BUDGET) over sports_category by department limit=0
| transpose 0 header_field=sports_category
| addtotals fieldname=TOTAL
| rename column as "Department"
| fillnull value="-"
| lookup "coach_lookup_name here" Department OUTPUT Coach
I've already setup the automatic lookup for it sir. I just need to call the "Coach" field created from the lookup but I can't...
Thank you for your response sir.
@rajyah , okie, thats because you dont have the fields in your chart and getting filtered out.
So either do the manual lookup after the chart command or include the coach field in the chart command, probably values(Coach) as Coach
Tried your response sir but I can't.. The field "Coach" which was created by automatic lookup is sitting idly in the fields list..