I am attempting to remove duplicate occurrences from a results table.
What I have
ID 1 NewBusiness $123
ID 1 NewBusiness $123 *
ID 1 Endorsement $156
ID 1 Endorsement $0.00
ID 1 Endorsement $0.00
ID 1 Close $279.00
ID 1 Close $279.00 *
What I would like to get too.
ID 1 NewBusiness $123
ID 1 Endorsement $156
ID 1 Endorsement $0.00
ID 1 Endorsement $0.00
ID 1 Close $279.00
The values marked with * are the duplicates I am trying to remove. The end game is to remove the duplicates for NewBusiness and Close whilst keep duplicates for Endorsement.
The field values are policyNumber (ID 1) / Stage (Close) / Premium ($123)
As you can see from the above it is not possible (at least I don't think it is) to use the dedup command as there is no one item to de duplicate on.
Is there anyway of achieving what I am trying to achieve ?
Cheers.
Alastair
Try
<your base search>|streamstats count current=t reset_on_change=true by policyNumber Stage|where Stage=="Endorsement" OR count==1
Please refine the by clause fields according to your final requirement
Try
<your base search>|streamstats count current=t reset_on_change=true by policyNumber Stage|where Stage=="Endorsement" OR count==1
Please refine the by clause fields according to your final requirement
Perfect... thank you so much. I cannot say how much this has helped.
Cheers
Alastair
Try like this | dedup policyNumber Stage Premium
But that will also remove the duplicate stage of Endorsement (which I want to keep.
In any policyNumber there should only ever be 1 NewBusiness, 1 Close but there can be multiple endorsements (I I need to keep these)