Hi @sultanulariff, We can use the appendpipe, eventstats, stats, and mvexpand commands to append a product of the set of Bag Type values by the set of Out Airline and Date values and then use the st...
See more...
Hi @sultanulariff, We can use the appendpipe, eventstats, stats, and mvexpand commands to append a product of the set of Bag Type values by the set of Out Airline and Date values and then use the stats command to summarize the Total Processed: | makeresults format=csv data="
Date,Out Airline,Bag Type,Total Processed
01/05/2024,IX,Local,100
01/05/2024,IX,Transfer,120
02/05/2024,BA,Local,140
02/05/2024,BA,Transfer,160
03/05/2024,IX,Local,150"
| appendpipe
[ eventstats values("Bag Type") as "Bag Type" ``` get all possible values of Bag Type ```
| stats values("Bag Type") as "Bag Type" by Date "Out Airline" ``` get all combinations of Bag Type by Date and Out Airline ```
| mvexpand "Bag Type" ``` expand all combinations into separate events ``` ]
| stats sum(eval(coalesce('Total Processed', 0))) as "Total Processed" by Date "Out Airline" "Bag Type" ``` sum Total Processed, replacing null values with 0 ``` Date Out Airline Bag Type Total Processed 01/05/2024 IX Local 100 01/05/2024 IX Transfer 120 02/05/2024 BA Local 140 02/05/2024 BA Transfer 160 03/05/2024 IX Local 150 03/05/2024 IX Transfer 0 This example will only append missing Bag Type values. It can be expanded to append any combination of Date, Out Airline, and Bag Type values. We can also convert Date to a time value and use the makecontinuous command to generate spans of dates prior to appending and summarizing combinations.