Alternative/Compliment to stats first()

[duesser]

| makeresults
| eval _raw="id;x;y;z;k
a;1;;;
a;;1;;
a;;;1;
a;2;;;
a;;2;;
a;;;;1
b;1;;;
b;;1;;
b;;;1;
b;2;;;
b;;2;;
b;;;;1
a;;1;;
a;;;1;
a;2;;;
a;;2;;
a;;;;1
b;1;;;
b;;1;;
b;;;1;
b;2;;;
b;;2;;
b;;;;1"
| multikv forceheader=1
| fields id x y z k
| table id x y z k
| stats first(*) AS * BY id

I have date of the following form, where the usual variables Z and K (might) have multiple measurements that are not unique, so I use "| stats first()" to aggregate them to the ID. However there are variables X and Y that do contain multiple unique values (which might also repeat). In the end I would like to obtain a table like so:

id	x1	x2	y1	y2	z	k
a	1	2	1	2	1	1
b	1	2	1	2	1	1

Where (ideally) the fields of X and Y are numbered for each unique value (NOT the value in the field) so that if 3 unique values are in the data it would yield X1,2,3.

[ITWhisperer · Accepted solution]

| table id x y z k
| stats list(x) as x list(y) as y first(z) AS z first(k) as k BY id
| eval x=mvdedup(x)
| eval y=mvdedup(y)
| eval xrange=mvrange(0,mvcount(x))
| mvexpand xrange
| eval name="x".(xrange+1)
| eval {name}=mvindex(x,xrange)
| eval yrange=mvrange(0,mvcount(y))
| mvexpand yrange
| eval name="y".(yrange+1)
| eval {name}=mvindex(y,yrange)
| fields - x xrange y yrange name
| stats values(*) as * by id

[duesser]

Great solution - I did not know about mvindex and mvrange! They seem like a usefull couple.

instead of list -> mvdedup you can just use values.

[ITWhisperer]

I went for list and mvdedup to preserve the order, if the order is not significant, then yes, values is just as good.