How to split single column into muliple columns and place them as adjecent colums?

[DarshanBK]

Hi All,

I have data as below, my requirement is to append/merge both the columns and then for each year split the column into multiple and place the details for one year field value adjacent to the previous one.

Merging part can be taken care, i need the solution for splitting columns part.

Can someone please help how to achieve this.

SPL:

|rex field=_raw "(?<Date>\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})"
| rex field=_raw "\w+:\s(?<Year>(\d+))\sQ"
| rex field=_raw "\d+\s(?<Quarter>(Q\d)):"
|rex field=_raw "\s+(?<Count>(\d+M))"
|table Year,Quarter,Count
|strcat Quarter " " Count Task
|fields - Quarter Count
|strcat Year " " Task Ask
|fields - Year Task

Below is the expected result.

2021           2022

Q4 2m         Q4 5m

Q3 1m         Q3 7m

Q2 2m         Q2 8m

Q1 0m         Q1 5m

[johnhuang]

| eval Count=LOWER(Count)
| fillnull value="0m" Count
| eval {Year}=Quarter." ".Count
| stats max(20*) AS 20* BY Quarter
| sort -Quarter
| table 20*

With test data:

| makeresults | eval data="2021,Q4,2m;2021,Q3,1m;2021,Q2,2m;2021,Q1,;2022,Q4,5m;2022,Q3,7m;2022,Q2,8m;2022,Q1,5m"
| eval data=split(data, ";") | mvexpand data
| rex field=data "(?<Year>\d+)\,(?<Quarter>\w+)\,(?<Count>\w+)?"
| table Year Quarter Count
| eval Count=LOWER(Count)
| fillnull value="0m" Count
| eval {Year}=Quarter." ".Count
| stats max(20*) AS 20* BY Quarter
| sort -Quarter
| table 20*

[PaulPanther]

Try it with  command xyseries like

| table _time,Year,Task
| xyseries Task Year Task
| fields - Task