topic Re: splitting many rows in columns in Dashboards & Visualizations

splitting many rows in columns

fabrizioalleva — Wed, 08 May 2019 13:51:41 GMT

Hello,
I need to transform this table:

Fruits Euro
Apples 2
Banana 1
Strawberry 3
Ananas 3
Blueberry 4
Pear 2

into :
Fruits Euro Fruits Euro
apples 2 Banana 1
Strawb 3 Ananas 3
Bluebe 4 Pear 2

Is there a way ?

Thanks Fabrizio

Re: splitting many rows in columns

dmarling — Wed, 08 May 2019 14:57:25 GMT

You cannot have the same field name in a table have multiple columns when making a table in Splunk. You would need to do a hack to get it to work by appending a space to the name so it appears to be the same but is actually different:

| makeresults count=1
| fields - _time
| eval data="Apples 2
Banana 1
Strawberry 3
Ananas 3 
Blueberry 4
Pear 2"
| rex field=data max_match=0 "(?<data>[^\n\e]+)"
| eval data=trim(data)
| mvexpand data
| rex field=data "(?<Fruits>[^\s]+) (?<Euro>\d+)"
| table Fruits Euro
| streamstats reset_after="count=2" count
| streamstats values(Fruits) as joiner window=1 current=f
| eval "Fruits "=if(count=2, Fruits, null())
| eval "Euro "=if(count=2, Euro, null())
| eval joiner=if(count=1, Fruits, joiner)
| eval Fruits=if(count=1, Fruits, null())
| eval Euro=if(count=1, Euro, null())
| stats values(Fruits) as Fruits values(Euro) as Euro values("Fruits ") as "Fruits " values("Euro ") as "Euro " by joiner
| fields - joiner

Re: splitting many rows in columns

niketn — Wed, 08 May 2019 16:05:51 GMT

@fabrizioalleva what is the criteria for moving rows to column?

Re: splitting many rows in columns

fabrizioalleva — Thu, 09 May 2019 07:30:46 GMT

@niketnilay : I have to order a small table with 12 lines, dividing them, for reasons of space in the dashboard, 6 in the first two columns and 6 in the second

Re: splitting many rows in columns

fabrizioalleva — Thu, 09 May 2019 07:36:12 GMT

PERFECT!!!!! I've to adjust it for my data!!
Thanks a lot

Re: splitting many rows in columns

dmarling — Thu, 09 May 2019 10:42:34 GMT

Glad it worked for you. I'm converting my comment to an answer. If you could please accept it once it updates, I would appreciate it. Thank you!