topic Re: how to re-arrange the column values to corresponding rows in table in Splunk Search

how to re-arrange the column values to corresponding rows in table

satish_tblocks — Fri, 04 May 2018 10:25:07 GMT

Hi All,

i have created the table & table is in below format...

i need to display the table like below format..

Can some please provide us the solution, how to achieve in this scenario...

Thanks,
gmvs

Re: how to re-arrange the column values to corresponding rows in table

FrankVl — Fri, 04 May 2018 12:17:56 GMT

What is the logic behind this transformation? I get the replacing NULL and empty fields by 0, but you're also moving some stuff around, based on what logic? You seem to completely ignore the first column?

So something like:

| eval Approved=if(Approved=="<NULL>" OR Approved=="",0,Approved)
| eval "PDF Values"=if('PDF Values'=="<NULL>" OR 'PDF Values'=="",0,'PDF Values')
| eval "Email Vales"=if('Email Vales'=="<NULL>" OR 'Email Vales'=="",0,'Email Vales')
| stats sum(Approved) as Approved sum("PDF Values") as "PDF Values" sum("Email Vales") as "Email Vales" by Area

Only thing is that will remove the first column. But since you seem to ignore that anyway, perhaps that is OK?

Re: how to re-arrange the column values to corresponding rows in table

satish_tblocks — Fri, 04 May 2018 13:37:14 GMT

Hi,

please ignore the NULL values, i need to move the "All 3 field values of Approved, PDF Values, Email Vales" to corresponding Area level only like,

Area CA=11 for Approved in first row &
Area DE=19 for Email Values in second row &
Area NJ=43 for Approved in sixth row......etc, (like in given table format) want to move the each corresponding "Area" values...

please provide us the solution...

Thanks,
gmvs

Re: how to re-arrange the column values to corresponding rows in table

FrankVl — Mon, 07 May 2018 06:37:47 GMT

Yeah, so you're summarizing by Area basically? (and cleaning up null/empty to be 0). Have you tried my solution? Because I think it does just that 🙂

Re: how to re-arrange the column values to corresponding rows in table

consultanteIman — Tue, 08 May 2018 12:59:34 GMT

hello,

If the values of the table are frozen, I think you can go through this solution :

| eval Approved=if((Approved=="NULL" OR isnull(Approved)),0,Approved)| eval "Email values"=if((Email=="NULL" OR isnull(Email)),0,Email) | eval "PDF values"=if(("PDF values"=="NULL" OR isnull("PDF values")),0,"PDF values") |eval Area=if(SNO=="3" AND Area=="CA","PR",Area)|dedup Area |table SNO,Area,Approved,"PDF values","Email values"