Split 2 fields into each rows

[Annna]

How to have split, i tired many ways but its coming out.

Output:

A	B	C
288136957	1	66871812
288137548	1	62919303
288137548	2	69101805
288137548	3	84124302
488136313	1	66871812
488136313	2	65252707
488136313	3	65602005
488136313	4	69101805

Thanks for the help.

[tread_splunk]

| makeresults 
| eval _raw="
A         B          C                          D
288813    1          45456                      car
388812    1|2|3|4    45456|12312|78978|12454    car|Rose|Toy|Bus
484784    1|2|3      12312|78978|12454          Rose|Toy|Bus" 
| multikv forceheader=1 
| fields - _time _raw linecount 
| eval B=split(B,"|"), C=split(C,"|"), D=split(D,"|") 
| eval E=mvzip(B,mvzip(C,D))

[gcusello]

Hi @Annna,

at first you could find a different way to aggregate data using the thats command, so instead to use

| stats values(B) AS B values(C) AS C BY A

you could use something like this:

| stats BY A B C

If this is non acceptable for you, you have to use the the mvexpand command to denormalize your table.

when you have only one field to denormalize it's easy :

| stats values(B) AS B values(C) AS C BY A 
| mvexpand B

The problem is when you have two or more fields multivalue (as your case) because the order could be different than the real pair fieldB/fieldC.

So you have to aggregate B and C before stats and then split after:

| eval temp=B."|".C
| stats values(temp) AS temp BY A 
| mvexpand temp
| rex field=temp "^(?<B>\d+)\|(?<C>\d+)"
| table A B C

Ciao.

Giuseppe

[Annna]

If the data be like this 

A	B	C	D
288813	1	45456	car
388812	1 2 3 4	45456 12312 78978 12454	car Rose Toy Bus
484784	1 2 3	12312 78978 12454	Rose Toy Bus

IN above scenario how it works split for the rows.

output be like:

A	B	C	D
288813	1	45456	car
388812	1	45456	car
388812	2	12312	Rose
388812	3	78978	Toy
388812	4	12454	Bus
484784	1	12312	Rose
484784	2	78978	Toy
484784	3	12454	Bus

[ITWhisperer]

It depends on whether you have access to the data before it is put into multi-value fields

| eval temp=B."|".C."|".D
| stats values(temp) AS temp BY A 
| mvexpand temp
| rex field=temp "^(?<B>[^\|]+)\|(?<C>[^\|]+)\|(?<D>[^\|]+)"
| table A B C D

If you don't, it depends if which version of splunk you are using and whether mvzip is available to you

| eval temp=mvzip(mvzip(B, C, "|"),D, "|")
| fields - B C D
| mvexpand temp
| rex field=temp "^(?<B>[^\|]+)\|(?<C>[^\|]+)\|(?<D>[^\|]+)"
| table A B C D