Give results by email and days of the week on one row with Yes, No or Null for the past week

[CSULeigh]

I am trying to get the data into a chart from an index were a user may answer a question daily. 

This is what I need to produce:

email	sun	mon	tue	wed	thu	fri	sat
bob@email.com	-	NO	NO	NO	Yes	-	-
aaron@email.com	Yes	-	-	NO	-	-	-
chuck@email.com	NO	NO	NO	Yes	NO	-	Yes

This i s what I am currently getting:

email	sun	mon	tue	wed	thu	fri	sat
bob@email.com	-	-	-	-	Yes	-	-
bob@email.com	-	-	-	NO	-	-	-
bob@email.com	-	-	NO	-	-	-	-
bob@email.com	-	NO	-	-	-	-	-
aaron@email.com	-	-	-	NO	-	-	-
aaron@email.com	-	-	-	NO	-	-	-
aaron@email.com	Yes	-	-	-	-	-	-
chuck@email.com	-	-	NO	-	-	-	-
chuck@email.com	-	-	-	-	NO	-	-
chuck@email.com	-	-	-	Yes	-	-	-
chuck@email.com	NO	-	-	-	-	-	-
chuck@email.com	-	NO	-	-	-	-	-
chuck@email.com	-	-	-	-	-	-	Yes

Here is what I have so far, but I am stuck and not to sure what process I should use:
index=someindex 
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval sun=if(qualdayOfWeek="Sun", $status$, "-")
| eval mon=if(qualdayOfWeek="Mon", $status$, "-")
| eval tue=if(qualdayOfWeek="Tue", $status$, "-")
| eval wed=if(qualdayOfWeek="Wed", $status$, "-")
| eval thu=if(qualdayOfWeek="Thu", $status$, "-")
| eval fri=if(qualdayOfWeek="Fri", $status$, "-")
| eval sat=if(qualdayOfWeek="Sat", $status$, "-")
| table email, sun, mon, tue, wed, thu, fri, sat

[thambisetty]

index=someindex 
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval sun=if(qualdayOfWeek="Sun", $status$, "-")
| eval mon=if(qualdayOfWeek="Mon", $status$, "-")
| eval tue=if(qualdayOfWeek="Tue", $status$, "-")
| eval wed=if(qualdayOfWeek="Wed", $status$, "-")
| eval thu=if(qualdayOfWeek="Thu", $status$, "-")
| eval fri=if(qualdayOfWeek="Fri", $status$, "-")
| eval sat=if(qualdayOfWeek="Sat", $status$, "-")
| stats values(status) as status by email, sun, mon, tue, wed, thu, fri, sat

[gcato · Accepted solution]

Hi @CSULeigh 

You want to use stats to group by email.  Give this a try...

index=someindex 
| eval qualdayOfWeek=strftime(_time, "%a")
| rex field=_raw "is\s(?<status>(NO|YES))\sto"
| eval {qualdayOfWeek}=case(
          qualdayOfWeek="Sun", $status$
         ,qualdayOfWeek="Mon", $status$
         ,qualdayOfWeek="Tue", $status$
         ,qualdayOfWeek="Wed", $status$
         ,qualdayOfWeek="Thu", $status$
         ,qualdayOfWeek="Fri", $status$
         ,qualdayOfWeek="Sat", $status$
         ,true(), "-" )   
| table email, Sun, Mon, Tue, Wed, Thu, Fri, Sat
| stats values(*) AS * BY email

I've also shown how to use an eval "case" statement with a field name substitution i.e. {field} - not that it really matters and could use eval.  You could also "rename" the colums to lowercase if you prefer (| rename Sun AS sun Mon AS mon Tue AS ...).

Also note that you if you time period goes over multiple weeks that you may have more than one result per week day column.

Hope this helps.