lose one event if another one exists using dedup

[darrend]

Hi Guys

I am trying to automatically create a lookup table based on results from searches, part of the search will be to feed results back in from the previously generated lookup, something like this:

search for some stuff | append [previousoutput.csv] |eval field3=if(is null(field3),"unknown",field3)

This gets me so far and gives me results like:

field1,field2,field3
hannah,green,banana
clive,red,unknown
hannah,green,unknown

This is where i am coming unstuck, i want the output lookup i generate off of the back of this to be reduplicated, 1 entry per user, but i only want to keep the amended version of field 3 if it exists, if an amended version of field 3 does not exist then i would like to populate the output with unknown, so my output csv would look like:

field1,field2,field3
hannah,green,banana
clive,red,unknown

Any ideas?

Thanks
Darren

[landen99]

yourstuff | eval field3=if(isnull(field3),"zzzzzzzz",field3) | sort limit=0 field3 | dedup keepempty=t field3 | eval field3=if((field3)="zzzzzzzz","unknown",field3)

[somesoni2]

Try following (assuming we get only two duplicate entries, one with amended value and one with "Unknown")

search for some stuff | append [previousoutput.csv] |eval field3=if(is null(field3),"unknown",field3)
 | dedup field1, field2, field3| mvcombine field3 delim="," |eval val1=mvindex(field3,0) | eval val2=mvindex(field3,1) | eval field3=case(val1="Unknown" AND isnotnull(val2), val2, 1=1,val1)

This combines field3 for duplicate values for field1 and field2 (field3 is different,field3=amendedValue and field3=Unknown) into one mv field and then takes the first non "unknown" value for it.

[Ayn]

Well, dedup? 🙂

... | dedup field1 field2

[darrend]

That's what i originally tried, but with that approach there is no guarantee that you won't end up with:

field1,field2,field3
hannah,green,unknown
clive,red,unknown

I may not of worded my original question very well, but i need to guarantee that the alternate value gets kept if the alternate and the "unknown" values both exist. I cannot do a sort as the alternate value will become a user controlled free text field in my app.