topic Re: Copy Field and remove duplicate in Getting Data In

Copy Field and remove duplicate

wessam — Tue, 29 Sep 2020 14:23:30 GMT

Hello All,

I have a column list of records as below

recordA
recordB
recordA
RecordB
RecordC
RecordD

and I would like to remove duplicate and copy them in another column to have finally a column for all records (records) and second record for records after dedup (record_after_dedup)
I tried to use eval with dedup but it doesn't work

eval record_after_dedup=dedup(records)

Re: Copy Field and remove duplicate

dineshraj9 — Wed, 07 Jun 2017 09:39:59 GMT

Do you want ignore the case and remove duplicates?

Re: Copy Field and remove duplicate

wessam — Wed, 07 Jun 2017 09:43:22 GMT

yes, i just need to have two columns the first one includes all the records and second one include the records after removing duplicates

Re: Copy Field and remove duplicate

dineshraj9 — Wed, 07 Jun 2017 09:47:47 GMT

The number of rows would differ once you have removed the duplicates, instead use a multi-valued field -

<your search> | eval record_new=lower(records) | stats values(records) as records,values(records_new) as records_after_dedup

Re: Copy Field and remove duplicate

wessam — Wed, 07 Jun 2017 10:06:03 GMT

Sorry i think there is misunderstanding , i am not asking to remove duplicates for lower and upper case or to ignore them , I am only asking to remove duplicates :
for Ex :
100
50
20
50
100
the above column contains all the record , i would like to have two columns first one "Records" for all the records and the second one "Records after dedup" for records after removing duplicate
to be as below
Records :
100
50
20
50
100
Records after dedup
100
50
20

Hope you get my point 🙂

Re: Copy Field and remove duplicate

dineshraj9 — Wed, 07 Jun 2017 10:23:05 GMT

Since you want it in two separate columns do it this way -

<your search > | table records | appendcols [ search <yoursearch> | dedup records | rename records as records_after_dedup ]

Re: Copy Field and remove duplicate

wessam — Tue, 29 Sep 2020 14:23:46 GMT

unfortunately it didn't work properly
index=xxx source="yyy.csv" |table records | appendcols [ index=xxx source="yyy.csv" | dedup records|rename records as records_after_dedup ]

it displayed all the records !

Re: Copy Field and remove duplicate

cmerriman — Wed, 07 Jun 2017 13:49:35 GMT

can you just do something like: ...|eval records_after_dedup=records|stats values(records_after_dedup) as records_after_dedup list(records) as records

Re: Copy Field and remove duplicate

wessam — Tue, 29 Sep 2020 14:24:26 GMT

when i write |eval records_after_dedup=records
it won't copy all records , it will only add new column called "records_after_dedup" where the name records is written as below

records_after_dedup
records
records
records
records
...

Re: Copy Field and remove duplicate

cmerriman — Tue, 29 Sep 2020 14:24:28 GMT

that's bizarre. try adding a single quote around records.
|eval records_after_dedup='records'

Re: Copy Field and remove duplicate

szabados — Tue, 29 Sep 2020 14:24:31 GMT

... | eval record_after_dedup=records | dedup record_after_dedup

Re: Copy Field and remove duplicate

szabados — Tue, 29 Sep 2020 14:24:34 GMT

... | eval record_after_dedup=records | dedup record_after_dedup

Re: Copy Field and remove duplicate

wessam — Tue, 29 Sep 2020 14:24:37 GMT

Thanks it works 🙂

eval records_dedup='records'|stats values(records) as records list(records_dedup) as records_dedup| dedup records_dedup | table records_dedup,records

Now it displayed two columns first one records_Dedup where records exist after removing duplicates and second column "records" where all records exist
i am trying now to display both of them using chart for specific month
| chart count over "date_month" by "records,records_dedup"
but now data is found 😞

Re: Copy Field and remove duplicate

cmerriman — Thu, 08 Jun 2017 14:16:50 GMT

if i'm understanding, the syntax should look more like :

eval records_dedup='records'|stats values(records_dedup) as records_dedup list(records) as records by date_month| table date_month records_dedup records

values lists the unique field values and list lists all field values.

i'm trying to understand what you're chart is supposed to look like. do you have an example?

Re: Copy Field and remove duplicate

wessam — Thu, 08 Jun 2017 14:41:02 GMT

i would like to display a graph that represent number of records_dedup and number of records over month

Re: Copy Field and remove duplicate

cmerriman — Thu, 08 Jun 2017 14:47:34 GMT

try adding |eval record_dedup_count=mvcount(records_dedup), records_count=mvcount(records)

Re: Copy Field and remove duplicate

wessam — Thu, 08 Jun 2017 15:13:55 GMT

it works ,much appreciated Thanks but it displayed also records_dedup and records in chart

Re: Copy Field and remove duplicate

cmerriman — Thu, 08 Jun 2017 15:15:42 GMT

if you'd like those fields removed, use |fields - records_dedup records

Re: Copy Field and remove duplicate

wessam — Thu, 08 Jun 2017 15:26:40 GMT

it works 🙂 Thanks for your help

Re: Copy Field and remove duplicate

somesoni2 — Thu, 08 Jun 2017 16:13:16 GMT

You could you mvdedup command like this (assuming you already have multivalued field

your current search giving multivalued field say 'mvfield'
| eval uniqmvfield=mvdedup(mvfield)

More of mvdedup here
http://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/MultivalueEvalFunctions#mvdedup.28X.29

If you don't have a multivalued field already, you can do like this

your current search giving field with single value say myfield
| stats list(myfield) as all_values values(myfield) as uniq_values