topic Re: combine multiple values of a table column based on other column value in Dashboards & Visualizations

combine multiple values of a table column based on other column value

architkhanna — Mon, 17 Sep 2018 06:33:15 GMT

I have table in my panel that has columns including owner,country,position,wbs.
Right now, seperate rows are made if one owner has multiple wbs.
I wanted to make a single row for a owner that has multiple wbs values and shown in a comma seprated wbs values.

Right now i have

owner wbs
abc 100
xyz 101
abc 102
abc 103

it should show

owner wbs
abc 100,102,103
xyz 101

Re: combine multiple values of a table column based on other column value

kamlesh_vaghela — Mon, 17 Sep 2018 06:56:02 GMT

@architkhanna,

Can you please try this?

YOUR_SEARCH | stats delim="," values(wbs) as wbc by owner | nomv wbc

Sample Search:

| makeresults | eval owner="abc",wbs="100" | append [| makeresults | eval owner="xyz",wbs="101"] | append [| makeresults | eval owner="abc",wbs="102"] | append [| makeresults | eval owner="abc",wbs="103"] | stats delim="," values(wbs) as wbc by owner | nomv wbc

[Edited Search]

| makeresults | eval owner="abc",wbs="100" | append [| makeresults | eval owner="xyz",wbs="101"] | append [| makeresults | eval owner="abc",wbs="102"] | append [| makeresults | eval owner="abc",wbs="103"] | stats delim="," values(wbs) as wbc latest(_time) as Time by owner | nomv wbc | sort Time | fields - Time

You can | sort Time as per your requirement.

Added payrate column

Try:

YOUR_SEARCH | stats delim="," values(wbs) as wbc latest(_time) as Time values(payrate) as payrate by owner | nomv wbc |nomv payrate | sort - Time | fields - Time

Sample:

| makeresults | eval owner="abc",wbs="100",payrate="72" | append [| makeresults | eval owner="xyz",wbs="101",payrate="21"] | append [| makeresults | eval owner="abc",wbs="102",payrate="65"] | append [| makeresults | eval owner="abc",wbs="103",payrate="82"] | stats delim="," values(wbs) as wbc latest(_time) as Time values(payrate) as payrate by owner | nomv wbc |nomv payrate | sort - Time | fields - Time

Thanks

Re: combine multiple values of a table column based on other column value

architkhanna — Mon, 17 Sep 2018 07:08:51 GMT

Thank you so so much.this works.

Re: combine multiple values of a table column based on other column value

architkhanna — Mon, 17 Sep 2018 07:14:22 GMT

Just a thought.
This messes up the order of columns. This new column have moved to almost end of the table for which I need to scroll (there are many fields and earlier it used to come in 4th place)
Does Splunk decide the order randomly.

Re: combine multiple values of a table column based on other column value

architkhanna — Mon, 17 Sep 2018 07:26:20 GMT

Also, I am getting duplicate rows now 😞

Re: combine multiple values of a table column based on other column value

kamlesh_vaghela — Mon, 17 Sep 2018 07:37:47 GMT

Added new search in Answer. Please check and revert. Accept and upvote this answer if it helps you.

Re: combine multiple values of a table column based on other column value

architkhanna — Mon, 17 Sep 2018 08:06:37 GMT

SO we are almost near.the table has many rows out of which I showed two collumns in example. Now using the following code
YOUR_SEARCH | stats delim="," values(wbs) as wbc by owner | nomv wbc

I am getting one row with wbs values comma seperated but like 101,102,101. Same value is repeated.this is due to the fact that I have two rows having same owner and same wbs but different other values.

owner wbs payrate
abc 100 72
xyz 101 21
abc 102 65
abc 100 82

it is showing

owner wbs payrate
abc 100,102,100 72
xyz 101 21

Re: combine multiple values of a table column based on other column value

kamlesh_vaghela — Mon, 17 Sep 2018 09:38:29 GMT

Please check updated answer.

Re: combine multiple values of a table column based on other column value

kamlesh_vaghela — Wed, 19 Sep 2018 09:24:51 GMT

@architkhanna,

Did you get chance to check my updated answer?