How to make headers as field-values?

[teddyidc1101]

I have a table that looks like this
Site 1 2 3 4 5 6

In Scope Onsite 3.5 2.44 2.4809851 1.164 2.3125 Local
In Scope Offshore 2 3 0.505 1.136 International

and I want to transform it to like this,

Site Phase effort month
In Scope Onsite Local 2.3125 6
In Scope Onsite Local 3.5 1
In Scope Onsite Local 2.44 2
In Scope Onsite Local 2.4809851 3
In Scope Onsite Local 1.164 4
In Scope Offshore International 2 1
In Scope Offshore International 3 2
In Scope Offshore International 0.505 3
In Scope Offshore International 1.136 4

Please advise. Thanks!

[oda · Accepted solution]

Is uptake going well?
I think that it will work with the following.

your search | rename 1 as test1 ,2 as test2,3 as test3 , 4 as test4 , 5 as test5 ,6 as test6 | eval Result=mvappend(test1,test2,test3,test4,test5,test6) | eval effort=mvfilter(match(Result,"\d+")) | mvexpand effort | streamstats count by Site | eval Phase=case(Site=="In Scope Offshore","International",Site=="In Scope Onsite","local") | rename count as month | table Site Phase effort month

[teddyidc1101]

works as expected!

[teddyidc1101]

thank you!

[oda]

thank you for your reply
it is nice!

[teddyidc1101]

hello! i encountered an issue on some of the occurrences while checking the results. the count should corresponds to a month,right? I extended the months to 12, some of them worked but some continued to 13 onwards moving to 60+. is there a way to limit the count per mv field?

[oda]

Can you define the field well?
Sample log and sample fields and
please tell me the expected behavior.

[teddyidc1101]

Sorry for the delayed response but this is working as expected...thanks so much for the help!

[oda]

That's good!
Thank you!