Combining separate fields to multi values

[thdose]

I have 300 match_ fields per event. Here are the first 9 from one event:

match_1="Don\'t Be So Shy" match_2="Imany" match_3="8,980,580" match_4="Human" match_5="Rag\'n\'Bone Man" match_6="763,862" match_7="Closer" match_8=" Chainsmokers" match_9="3,671,646"...etc

I want to structure the result so I'll get triplets of data as:

title="Don\'t Be So Shy" name="Imany" score="8,980,580" 
title="Human" name="Rag\'n\'Bone Man" score="763,862" 
title="Closer" name="Chainsmokers" score="8,980,580"...etc

I am messing around with modulus and mv functions in order to first connect the 3 relating fields and then expand to simulate the triplets as each their event, but with no succes.

Any help would be much appreciated.

[maciep]

Not sure if this will work, but if the data is consistent maybe you can use rex to along with some mv commands

| stats count | eval blah="match_1=\"Don\'t Be So Shy\" match_2=\"Imany\" match_3=\"8,980,580\" match_4=\"Human\" match_5=\"Rag\'n\'Bone Man\" match_6=\"763,862\" match_7=\"Closer\" match_8=\" Chainsmokers\" match_9=\"3,671,646\"" 
| rex field=blah "match\_\d+=(?<title>\"[^\"]+\")\s+match\_\d+=(?<name>\"[^\"]+\")\s+match\_\d+=(?<score>\"[^\"]+\")" max_match=0 
| eval temp = mvzip(title,name) 
| eval temp = mvzip(temp,score) 
| fields temp 
| mvexpand temp 
| rex field=temp "^(?<title>[^,]+),(?<name>[^,]+),(?<score>.+)" 
| fields - temp 
| table title name score

[somesoni2]

Give this a try

your base search | table match_* | eval temp=1 | untable temp fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | streamstats count as label by rank |
eval label=case(label=1,"title", label=2,"name",true(),"score") | chart values(fieldvalue) over rank by label

Update

your base search | table match_* | eval temp=1 | accum temp| untable temp fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | eval rank=temp."-".rank | streamstats count as label by rank | eval label=case(label=1,"title", label=2,"name",true(),"score") | chart values(fieldvalue) over rank by label | fields - rank

[DalJeanis]

The problem is that...

| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3)

...needs to be...

| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+2)/3)

...and it works just fine.

Run anywhere demonstration code -

| makeresults count=100 
| streamstats count as recno 
| eval recgroup = ceiling((1+recno)/10) 
| eval recgroup="A".if(len(recgroup)=1,"00","0").recgroup
| eval suffix=case(len(recno)=3,recno,len(recno)=2,"0".recno,true(),"00".recno) 
| eval name="name".suffix,song="song".suffix,score=8000000+recno
| eval stuff=mvappend(song,name,score)
| table stuff recgroup
| stats list(stuff) as stuff by recgroup
| mvexpand stuff
| streamstats count as recno
| eval suffix=case(len(recno)=3,recno,len(recno)=2,"0".recno,true(),"00".recno) 
| eval fieldname="match_".recno
| eval {fieldname} = stuff
| stats values(match*) as match*
| rename COMMENT as "The above just generates test data"

| rename COMMENT as "and we try somesoni2's last solution, with +1 changed to +2"
| untable recno fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+2)/3) 
| streamstats count as label by rank 
| eval label=case(label=1,"title", label=2,"name",true(),"score") 
| chart values(fieldvalue) over rank by label

... generating ...

rank   name          score        title
1         name001   8000001   song001  
2         name002   8000002   song002  
3         name003   8000003   song003  

... etc

[thdose]

Thanks. Almost, but not quite. It seems some values get mixed up:

[thdose]

[somesoni2]

Seems like there are some null values for some of the fields due to which the order of assignment is failing. Can you confirm if that is the case (try to run everything before the last chart command in the search and see the result).

[thdose]

If can't seem to find any null values. Here are the first fields in the first event:

raw_match_count="300" match_1="Don\'t Be So Shy (Filatov & Karas Remix)" match_2="Imany" match_3="8,980,580" match_4="Human" match_5="Rag\'n\'Bone Man" match_6="763,862" match_7="Closer" match_8="The Chainsmokers Feat. Halsey" match_9="3,671,646" match_10="Dancing On My Own" match_11="Calum Scott" match_12="678,533" match_13="Starboy" match_14="The Weeknd Feat. Daft Punk" match_15="802,807" match_16="Still Falling For You" match_17="Ellie Goulding" match_18="581,123" match_19="The Greatest" match_20="Sia Feat. Kendrick Lamar" match_21="926,971" match_22="Sexual" match_23="NEIKED" match_24="108,857" match_25="Starving" match_26="Hailee Steinfeld & Grey Feat. Zedd" match_27="758,657" match_28="Heathens" match_29="Twenty One Pilots" match_30="3,066,742"

[somesoni2]

Can you provide a screenshot of following query? need to see first few lines as they are the one which are not working.

your base search | table match_* | eval temp=1 | accum temp| untable temp fieldname fieldvalue  | eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | eval rank=temp."-".rank | streamstats count as label by rank | eval label=case(label=1,"title", label=2,"name",true(),"score")

[thdose]

Sure:

[somesoni2]

Try the updated answer.