Hi, I wonder whether someone may be able to help me please.
I'm running the query below which works fine.
index=main auditSource="iht" auditType=Questionnaire "detail.version"=1 | rename detail.activity AS activity, detail.easytouse AS select, detail.nino AS nino | eval activity=if(activity=="","Not filled",activity) | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", 1=1, activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats list(activity) as activityList, values(select) as selectList, values(generatedAt) as timeList by nino, generatedAt| fields nino, timeList, activityList, selectList| sort nino, timeList
But what I'm trying to do is now group this by the nino field.
I've tried changing the final two pipes with this:
| stats count by nino | fields nino, timeList, activityList, selectList
But the problem is, is that although I can see the nino values, all the other fields are blank i.e. timeList, activityList, selectList
I just wondered whether someone may be able to tell me where I've gone wrong please.
Many thanks and kind regards
Chris
All, I just wanted let you know I've fixed this with:
index=main auditSource="iht-frontend" auditType=Questionnaire "detail.version"=1 | rename detail.activity AS activity, detail.easytouse AS select, detail.nino AS nino | eval activity=if(activity=="","Not filled",activity) | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", 1=1, activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | eval time=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M.%S" ctime(time) | stats list(activity) as activityList, values(select) as selectList, values(time) as timeList by nino, time | fields nino, timeList, activityList, selectList| sort nino, timeList| rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats values(activityList) values(selectList) by timeList
This doesn't group by nino as I would have liked but I went for second best and grouped by the "timeList" i.e. "generatedAt" time.
Many thanks and kind regards
Chris
This should work:
index=main auditSource="iht" auditType=Questionnaire "detail.version"=1 | rename detail.activity AS activity, detail.easytouse AS select, detail.nino AS nino | eval activity=coalesce(activity, "Not filled") | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", true(), activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats list(activity) as activityList, values(select) as selectList, values(generatedAt) as timeList by nino
Hi @woodcock, thank you for coming back to me with this, but unfortunately it didn't work properly.
Although, this did group by the nino, it took away the correct values for the associated "activityList" and "selectList".
Many thanks and kind regards
Chris
All, I just wanted let you know I've fixed this with:
index=main auditSource="iht-frontend" auditType=Questionnaire "detail.version"=1 | rename detail.activity AS activity, detail.easytouse AS select, detail.nino AS nino | eval activity=if(activity=="","Not filled",activity) | makemv allowempty=true delim="," activity| mvexpand activity | eval activity = case(activity ==1, "Register", activity==2, "Provide asset information", activity==3, "Provide gift information", activity==4, "Provide debt information", activity==5, "Provide exemption information", activity==6, "Increase Threshold", activity==7, "Check estate report", activity==8, "Declare and submit application", activity==9,"Request clearance", 1=1, activity) | eval select = case(select ==1, "Very easy", select==2,"Easy",select==3,"Neither easy nor difficult",select=4,"Difficult",select==5,"Very difficult",select=="","Not filled") | eval time=strptime(generatedAt, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y %H:%M.%S" ctime(time) | stats list(activity) as activityList, values(select) as selectList, values(time) as timeList by nino, time | fields nino, timeList, activityList, selectList| sort nino, timeList| rex field=nino mode=sed "s/(\S{1})\S{1}/\1X/g" | stats values(activityList) values(selectList) by timeList
This doesn't group by nino as I would have liked but I went for second best and grouped by the "timeList" i.e. "generatedAt" time.
Many thanks and kind regards
Chris
You should click "Accept" for the best answer.