Archive

Group By Field

Motivator

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

0 Karma
1 Solution

Motivator

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

View solution in original post

0 Karma

Esteemed Legend

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
0 Karma

Motivator

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

0 Karma

Motivator

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

View solution in original post

0 Karma

Esteemed Legend

You should click "Accept" for the best answer.

0 Karma