Splunk Search
Highlighted

how to transpose 2 column

New Member

......||addcoltotals | table * | reverse | head 1
1AusgustR, 2AusgustR ,1AusgustL,2AusgustL
26 30 15 27

want to have table like below

Date Number1 Number 2
1August, 26 15
2
August, 30 27

Tags (1)
0 Karma
Highlighted

Re: how to transpose 2 column

Champion

Sorry I did not notice the previous question.
Try this!

......|addcoltotals | table * | reverse | head 1 
|transpose|rename column as Date,"row 1" as Number
|eval key=if(substr(Date,len(Date),1)="L","Number2","Number1")
|eval Date=substr(Date,1,len(Date)-2)
|stats sum(eval(if(key="Number1",Number,0))) as Number1,sum(eval(if(key="Number2",Number,0))) as Number2 by Date

View solution in original post

0 Karma
Highlighted

Re: how to transpose 2 column

New Member

Thanks, it works

0 Karma
Highlighted

Re: how to transpose 2 column

SplunkTrust
SplunkTrust

Try this...

| makeresults | eval 1_Ausgust_R=26 | eval 2_Ausgust_R=30 | eval 1_Ausgust_L=15| eval 2_Ausgust_L=27
| rename COMMENT as "The above generates your test data."

| streamstats count as recno
| untable recno myfield myvalue
| rex field=myfield "^(?<Date>\d+_[^_]+)_(?<which>R|L)$"
| eval Number1=case(which="R",myvalue) 
| eval Number2=case(which="L",myvalue)
| stats values(Date) as Date values(Number*) as Number* by recno
| fields - recno
0 Karma