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 |
| 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.
| 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.
thanks a lot @to4kawa 🙂