How to unite multiple columns into one column?

[krim]

table A

krim_0-1664425523884.png

table B

krim_1-1664425551716.png

 I know there are lots of ways to spread the table from table B to table A . Is there ant method to transform table A to table B in Splunk without losing any data? like unite in R, pivot in BQ?

[bowesmana · Accepted solution]

The untable command does that - see this example

| makeresults
| eval _raw="ID,A,B,C
1122,1,2,5
1144,2,3,5
1155,1,9,4"
| multikv forceheader=1 
``` This is your table A ```
| table ID, A, B, C
``` Now convert to your table B ```
| untable ID pick number

[krim]

Hi, I try to apply untable on a table like this
|untable id class pick num

krim_0-1664435047043.png

I try to get a table like this ,but it doesn't work.

krim_1-1664435088795.png

Is there any way to solve this one?

[bowesmana]

To use untable to add an extra dimension, you would need to combine the fields forming the left hand item, like this

| makeresults
| eval _raw="ID,Class,A,B,C
1122,C1,1,2,5
1144,C2,2,3,5
1155,C3,1,9,4"
| multikv forceheader=1 
``` This is your table A ```
| eval ID=ID.":".Class
| table ID A B C

``` Now convert to your table B ```
| untable ID pick number
| rex field=ID "(?<ID>[^:]*):(?<Class>.*)"
| table ID Class pick number

which is creating the ID fields as ID+":"+Class and then untabling and then splitting back out the Class after the untable.

There would be other ways to do the same thing in Splunk - there is always more than one way... Here is another way to do it with the additional class field

| makeresults
| eval _raw="ID,Class,A,B,C
1122,C1,1,2,5
1144,C2,2,3,5
1155,C3,1,9,4"
| multikv forceheader=1 
``` This is your table A ```
| table ID Class A B C
``` Now combine all the row values to a single field 'Values' and remove the original fields ```
| eval Values=mvappend(A, B, C)
| fields - A B C
``` and now expand those values ```
| mvexpand Values

[krim]

that works thx!!!!