How do you create dummy values in a trellis chart?

[tseale]

I have locations 1-6, and I am needing them to stay in the same spot, even if in the time event, there is not a quantity value in the location.

index="data" 
| stats sum(Quantity) by Location

It is confusing on the floor when Location 6 is the 3rd tile instead of being on the end. I am needing to create placeholders that are blank for 1-6 that get updated as they start up.

[niketn · Accepted solution]

@tseale try the following search and confirm. It creates 6 rows with Location 1, 2,3,4,5,6 and respective Quantity as 0. Then dedup is used to retain Location with Quantity from index search and missing Locations with Quantity as 0. I have applied a sort for statistical table but trellis should sort by default.

index="data" 
| stats sum(Quantity) as Quantity by Location
| append [| makeresults count=6
| fields - _time
| streamstats count as Location
| eval Quantity=0]
| dedup Location
| sort Location

[tseale]

That worked, only change I had to make was |sort Location to get them in the right order.

Thank you so much!

[tseale]

[tseale]

I am wanting a box for 5 with the value blank or 0 in between box 4 and 6

[niketn]

@tseale what are the 6 location names? Are they static or dynamic? Can there be more than 6? Is there is master inventory configuration like lookup file, KV Store or Database table where all 6 locations (if required more) would be listed?

[tseale]

There is always 6. The quantity is aggregated throughout the day. When we pull last 24 hours they rarely all have a quantity for that day. so the they are in different locations.

[niketn]

Sorry still a bit confusing. Could you elaborate with a mock up of what you have and what is the expected output?