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:
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:
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.
That's what i originally tried, but with that approach there is no guarantee that you won't end up with:
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.