I want create table view

[abi2023]

I have SPL
index=main state=open | stats count(state) as open by risk_rating | rename risk_rating as state | addtotals col=t row=f labelfield=state | append [ search index=main state=closed | stats count(state) as closed by risk_rating_after | rename risk_rating_after as state | addtotals col=t row=f labelfield=risk 

I want crate table like below but risk_rating_after field only has Sustainable value so when I do selfjoin state 
it only has sustainable. I try join but it did not get result. It any way I can achieve this result.  

Many thank you in advance. 

state	Critical	Moderate	Severe	Sustainable	Total
Open	1	2	4	5	12
Close	0	0	0	6	6

Total                              1                                   2                                   4                                   11                                18

[yeahnah]

Hi @abi2023 

This is a good example of using the chart command.  Here's a run anywhere example (based of dummy events derived from your example SPL)

| makeresults | eval _raw="state, risk_rating
open, Critical
open, Moderate
open, Severe
open, Sustainable
close, Critical
close, Moderate
close, Sustainable"
| multikv forceheader=1
| table state risk_rating
  ``` ^^^ above is just creating dummy example events ^^^ -> the SPL below creates the table output ```
| chart count OVER state BY risk_rating
| sort - state
| addtotals
| addcoltotals labelfield=state label="Total"

 Hope this helps