Move values in columns to top independently?

[skriticos2]

Hi,

I'm a bit stuck with a data transformation. I got it to a point where all the columns and values are in the right order, but the rows are offset for each column.
I need to find a way to move the all the values in subsequent columns up to the top so they align.
See below example of before/after data alignment I want to realize.
Thanks for ideas!

f1      f2      f3      f4
----------------------------
foo
bar
baz
        x
        y
                a
                b
                        1
                        2
                        3
                        4

                        
                        
f1      f2      f3      f4
----------------------------
foo     x       a       1
bar     y       b       2
baz                     3
                        4

[peter_krammer]

Here is another solution for the originally requested example:

generate sample events:

| makeresults  count=11
| streamstats count
| eval f1 = case(count=1, "foo", count=2, "bar", count=3, "baz")
| eval f2 = case(count=4, "x", count=5, "y")
| eval f3 = case(count=6, "a", count=7, "b")
| eval f4 = case(count=8, "1", count=9, "2", count=10, "3", count=11, "4")
| fields - count, - _time

output: 

f1      f2      f3      f4
----------------------------
foo
bar
baz
        x
        y
                a
                b
                        1
                        2
                        3
                        4

solution SPL:

<yoursearch>
| foreach * [| eval field = if(isnotnull(<<FIELD>>), "<<MATCHSTR>>", field )]
| streamstats count as index by field
| stats values(*) as * by index
| fields - field, - index

output:

f1      f2      f3      f4
----------------------------
foo     x       a       1
bar     y       b       2
baz                     3
                        4

[peter_krammer]

Note: This option only works correctly if every row in the input has only 1 of the fields with a value. 

[peter_krammer]

I tried to solve this for you, but the query might be a bit inefficient:

| makeresults
| eval a = mvappend("a1", "a2", "a3"), b = mvappend("b1", "b2"), c = mvappend("c1", "c2", "c3", "c4" )

| rename * as *_values
| foreach *_values [| eval max = if(max > mvcount(<<FIELD>>), max, mvcount(<<FIELD>>) )]
| foreach *_values [| eval <<MATCHSTR>>_filler = mvrange(mvcount(<<FIELD>>), max, 1), <<MATCHSTR>>_filler = mvmap(<<MATCHSTR>>_filler, " "), <<FIELD>> = mvappend(<<FIELD>>, <<MATCHSTR>>_filler)]
| foreach *_values [| eval <<FIELD>> = mvmap(<<FIELD>>, "<<MATCHSTR>>=\"" . <<FIELD>> . "\"")]
| fields - *_filler
| eval raw = mvmap(mvrange(0, max, 1), " ")
| foreach *_values [| eval raw = mvzip(raw, <<FIELD>>)]
| fields raw
| mvexpand raw
| rename raw to _raw
| extract

[VatsalJagani]

Thanks, @peter_krammer  

This solution works! @skriticos2 - please consider accepting the solution.

[peter_krammer]

The code above solves this example:

header, values (mv field)

a = { a1, a2, a3 }
b = { b1, b2 }
c = { c1, c2, c3, c4 }

target table (each value in separate row/field)
a       b       c
-------------------
a1      b1      c1
a2      b2      c2
a3              c3
                c4

[renjith_nair]

Knowing your current search would be useful to construct the final output. However, try below

your search|stats values(*) as * by "your grouping field"

There should be a field to co-relate the data , for e,g "y" should be matched to bar,b and 2 for consistency in the result

[skriticos2]

Thanks, but I've been there (actually that is a previous transformation to this). Having the data in multi value fields does not help me so far, as I want to expand them to a number of new columns (based on the number of values in the multi value field). And each row has a different number of values between 10 and 70. Something like an mvexpand command, but sideways would help me (creating new fields for each value), but I'm not aware of how to achieve that.

[skriticos2]

To illustrate from another perspective, I have a table where one column contains the field name and another the values (all properly sorted). Now I want to create a transposed table where each of the values goes into a separate row for the header that it belongs to. Tricky part is, that the number of values is variable. I think I have a dimension too much here for a simple mvexpand. A sideways mvexpand would help. So, I'm a bit stuck.

header, values (mv field)

a = { a1, a2, a3 }
b = { b1, b2 }
c = { c1, c2, c3, c4 }

target table (each value in separate row/field)
a       b       c
-------------------
a1      b1      c1
a2      b2      c2
a3              c3
                c4

[skriticos2]

Something like this would also work. Create a new column for each value in an mvfield, then use a transpose afterward. Is that possible? (I mean something like a foreach statement - not if/case - I have up to 70 values)

header  values (mv)
-------------------
a       a1 a2 a3
b       b1 b2
c       c1 c2 c3 c4


header  values (mv)     r1      r2      r3      r4
--------------------------------------------------
a       a1 a2 a3        a1      a2      a3
b       b1 b2           b1      b2
c       c1 c2 c3 c4     c1      c2      c3      c4