Generate Matrix from Table Data

[jean_boulanger]

Hi,

I am currently working with a table that looks like this:

col1 | col2 | value
xA  | yA | 1.0
xA | yB | 1.5
xB | yA | 2.0
xB | yB | 2.5

From this table, I would like to create a matrix, like so:

  /  | yA | yB
xA | 1.0 | 1.5
xB | 2.0 | 2.5

I tried to use the transpose command, but I have been rather unsuccessful so far. Is this possible with Splunk?

[cmerriman · Accepted solution]

try adding this to your query:

|xyseries col1 col2 value

i used this runanywhere command for testing:

|makeresults|eval data="col1=xA,col2=yA,value=1.0 col1=xA,col2=yB,value=1.5 col1=xB,col2=yA,value=2.0 col1=xB,col2=yB,value=2.5"|makemv data|mvexpand data|rename data as _raw|kv|table col1 col2 value|xyseries col1 col2 value

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries

[jean_boulanger]

This worked like a charm, thank you

[nryabykh]

Hi, Jean.

Xyseries command implements exact what you need: https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Xyseries

[davpx]

untable might help