I'm trying to figure out how to build an excel-like pivot table using 3 or more columns. As example, I have this data:
customer1 project1 note1
customer1 project2
customer1 project3 note2
customer2 project4 note3
customer2 project5
customer2 project6 note4
customer2 project7 note5
customer3 project8
And I want to build a report that looks like this:
customer1 project1 note1
project2
project3 note2
customer2 project4 note3
project5
project6 note4
project7 note5
customer3 project8
I've been playing with stats list() and values() and both lose the relationship between the second and third columns.
| makeresults 1
| eval pickle="customer1,project1,note1|customer1,project2,|customer1,project3,note2|customer2,project4,note3|customer2,project5,|customer2,project6,note4|customer2,project7,note5|customer3,project8"
| eval pickle=split(pickle,"|")
| mvexpand pickle
| eval pickle=split(pickle,",")
| eval customer=mvindex(pickle,0)
| eval project=mvindex(pickle,1)
| eval note=mvindex(pickle,2)
| fields - _time pickle
| stats list(project) as project list(note) as note by customer
Notice the highlighted notes fall under the wrong project. I get the same behavior with stats values().
Is there a way to go about getting a multi column relational list without repeating the customer name in the output?
Any ideas anyone has are greatly appreciated!
... View more