How to remove duplicate values only in one column out of four columns?

[im_bharath]

Hello All, 

When using the "stats count by column1, column2, column3, column4"

I get the below result 

Existing table : 

column1	column2	column3	column4
XXXXXXXX	YYYYY	A	123
XXXXXXXX	YYYYY	B	123
XXXXXXXX	YYYYY	C	123
XXXXXXXX	YYYYY	D	123
XXXXXXXX	YYYYY	E	123

Where as I need this result :

column1	column2	column3	column4
XXXXXXXX	YYYYY	A	123
		B
		C
		D
		E

Could somebody please help me with the query? 

Thanks, 

[gcusello · Accepted solution]

Hi @im_bharath,

please try something like this:

<your_search>
| stats 
   count AS column4 
   values(column2) AS column2 
   values(column3) AS column3 
   BY column1

Ciao.

Giuseppe

[im_bharath]

Hey @gcusello, 

That worked.. thanks a lot. 

[kamlesh_vaghela]

@im_bharath 

Can you please try this?

YOUR_SEARCH
| streamstats count by column1 column2	column4
| eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"")
|fields - count

My Sample Search :

| makeresults 
| eval _raw="column1	column2	column3	column4
XXXXXXXX	YYYYY	A	123
XXXXXXXX	YYYYY	B	123
XXXXXXXX	YYYYY	C	123
XXXXXXXX	YYYYY	D	123
XXXXXXXX	YYYYY	E	123
1XXXXXXXX	1YYYYY	A	1234
1XXXXXXXX	1YYYYY	B	1234
1XXXXXXXX	1YYYYY	C	1234
1XXXXXXXX	1YYYYY	D	1234
1XXXXXXXX	1YYYYY	E	1234" 
| multikv forceheader=1
| table column1	column2	column3	column4
| rename comment as "upto this is sample data"
| streamstats count by column1 column2	column4
| eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"")
|fields - count

I hope this will help you.

 Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[im_bharath]

Hey @kamlesh_vaghela ,

I have tried below SPL query as suggested by you, 

| multikv forceheader=1
| table column1	column2	column3	column4
| rename comment as "upto this is sample data"
| streamstats count by column1 column2	column4
| eval column1=if(count==1,column1,""), column2=if(count==1,column2,""),column4=if(count==1,column4,"")
|fields - count

so i got below result similar to the other post, but we were getting the multiple values for column3 like 1,1,2,3,3,3,3,4,5,5,5,6,6, instead of 1,2,3,4,5,6 

So i have added the "dedup column3" to your search and it removed the duplicate results. 

Thank you for the solution. 

[im_bharath]

Hey @kamlesh_vaghela, 

I will try and let you know if this works as well or not..