What data transform approach should I use with this? MV, tranpose, ...?

[ChioNeng]

Im kinda newbie here in splunk. Whats the difference between multivalue and transpose command? how can i convert this input to this desired output? (see tables below) Is Multivalue or transpose command can manage this?

For a newbie like me those commands is kinda tricky. Many thanks in advance

input:

Field1	Field2	Country	1-Jun	1-Jul	1-Aug	1-Sep	1-Oct
X	AB	Thailand	23	2343	31	1	1
Y	CD	Singapore	2	321	2	52	22
Z	EF	Philippines	31	56	43	84	23

output:

Field1	Field2	Country	Month	Value
X	AB	Thailand	1-Jun	23
X	AB	Thailand	1-Jul	2343
X	AB	Thailand	1-Aug	31
X	AB	Thailand	1-Sep	1
X	AB	Thailand	1-Oct	1
Y	CD	Singapore	1-Jun	2
Y	CD	Singapore	1-Jul	321
Y	CD	Singapore	1-Aug	2
Y	CD	Singapore	1-Sep	52
Y	CD	Singapore	1-Oct	22
Z	EF	Philippines	1-Jun	31
Z	EF	Philippines	1-Jul	56
Z	EF	Philippines	1-Aug	43
Z	EF	Philippines	1-Sep	84
Z	EF	Philippines	1-Oct	23

[to4kawa · Accepted solution]

| makeresults 
| eval _raw="Field1	Field2	Country	1_Jun	1_Jul	1_Aug	1_Sep	1_Oct
X	AB	Thailand	23	2343	31	1	1
Y	CD	Singapore	2	321	2	52	22
Z	EF	Philippines	31	56	43	84	23" 
| multikv 
| table Field1 Field2 Country 1_Jun 1_Jul 1_Aug 1_Sep 1_Oct 
| foreach 1_* 
    [ rename <<FIELD>> as 1-<<MATCHSTR>>] 
| rename COMMENT as "this is your sample. from here, the logic"    
| eval tmp=mvzip(Field1,mvzip(Field2,Country)) 
| fields - F* Country 
| untable tmp Month Value 
| rex field=tmp "(?<Field1>[^,]+),(?<Field2>[^,]+),(?<Country>.*)" 
| fields - tmp 
| eval Month=strptime(Month."2019","%d-%b%Y") 
| table Field* Country Month Value 
| fieldformat Month=strftime(Month,"%d-%b") 
| sort Field1 Month

mvexpand or tranpose is not need.

[ChioNeng]

thanks a lot @to4kawa 🙂