How to perform chart command with two multifield columns in the table?

[Ashwini_5]

I performing the chart command for the below kind of table. 

Command : [|Chart  values(course) as course  over ID by status]

Ashwini_5_0-1671099564575.png

Received Output as BELOW: 

Ashwini_5_1-1671099713528.png

Expected Output : 

Ashwini_5_2-1671099792212.png

Kindly help to resolve this . I have tried |MVExpand  status also . . But it is picking only the first value and providing wrong output .  

[VatsalJagani]

@Ashwini_5 - Here is fully dynamic query that works with N number of course and status values:

| eval combined=mvzip(course, status, "|")
| mvexpand combined
| eval course=mvindex(split(combined, "|"), 0), status=mvindex(split(combined, "|"), 1)
| chart values(status) as status over ID by course

VatsalJagani_0-1671201928367.png

I hope this helps!!!

[FelixLeh]

Hey @Ashwini_5 !

I'm Assuming that both Course and status are multivalve fields.
If my assumption is true the following search should hopefully be able to aid you in finding the solution.

| makeresults 
| fields - _time
| eval ID = 1, course = "Course A,Course B,Course C",status="Expired OK Expired"
| append
    [| makeresults 
| fields - _time
| eval ID = 2, course = "Course A,Course B,Course C",status="OK OK Expired"]
| append
    [| makeresults 
| fields - _time
| eval ID = 3, course = "Course A,Course B,Course C",status="OK OK OK"]
| fields ID course status
| makemv course delim=","
| makemv status delim=" "
| mvexpand course
| eval status=case(course="Course A",mvindex(status,0,0),course="Course B",mvindex(status,1,1),course="Course C",mvindex(status,2,2))
| chart values(status) as status over ID by course