How can I aggregate information into rows based on a transaction ID?

[rpecka]

I have events which will all have an ID, stageID, stageDuration, as well as other information.

In the past I've used `chart sum(stageDuration) over ID by stageID` to create stacked bar charts, but now I’m more interested in the table produced by that command. It has a column for ID, then columns for the durations of each of the stageIDs that had the same ID.

I would like to create a similar table except I would like to include additional columns. For example, I would like to conditionally populate a column with event.startTime from the stage foo if the stageID foo exists for an ID.

I don't need to visualization, just the way of forming the table.

It seems like there might be a way to do this with transactions but I haven't been able to figure it out.

[ITWhisperer]

Try something like this

| eval foostart=if(stageID="foo", 'event.startTime',null())
| stats sum(stageDuration) as stageTotalDuration values(foostart) as foostart by ID stageID

[rpecka]

This isn't quite it.

When I run the chart command I get this table:

sessionID	stageID1	stageID2	stageID...
id1	99	86	34
id2	33	45	22

I want the same thing except without the chart and with additional columns populated depending on what the stages are

[ITWhisperer]

Can you give an example of what you are trying to achieve?

[rpecka]

I have events with stageIDs and IDs. I want to create one row for each ID which includes a column for each stageID to indicate whether it has or does not have a corresponding event for that stageID. Then I want to filter for only the rows (IDs) that have stageID=foo present, and then create a pie chart to count the occurrences of each stageID in the rows that remain

[ITWhisperer]

| chart count by id stageid
| where foo!=0