Deployment Architecture

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

DarshanBK
Explorer

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

DarshanBK_2-1668609170962.png

 

Below is the expected result.

2021           2022

Q4 2m         Q4 5m

Q3 1m         Q3 7m

Q2 2m         Q2 8m

Q1 0m         Q1 5m

Labels (3)
0 Karma

johnhuang
Motivator
| 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*

 

0 Karma

PaulPanther
Motivator

Try it with  command xyseries like

 

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

 

0 Karma
Get Updates on the Splunk Community!

.conf25 Community Recap

Hello Splunkers, And just like that, .conf25 is in the books! What an incredible few days — full of learning, ...

Splunk App Developers | .conf25 Recap & What’s Next

If you stopped by the Builder Bar at .conf25 this year, thank you! The retro tech beer garden vibes were ...

Congratulations to the 2025-2026 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...