Filter MV field for specified value

[dtaylor]

I've been smashing my head against this issue for the past few hours. I need to check a multivalue field to see if it contains the "N/A" *and* any value that isn't "N/A". If this is true, I need to filter whatever "N/A" exist within the field and return the remaining non-N/A values as a multivalue field.

[PickleRick]

What do you mean by "check"? Do you filter your initial results so that you have only those where field F contains at least two values of which one is 'N/A' and one isn't? Or do you want to do a conditional evaluation? (All other values which do not contain 'N/A' are left as they were).

[dtaylor]

Thnak you for your help.

For example, If I have a MV field with the values "red", "blue", "N/A", "N/A" I would want to filter out the "N/A" fields.

However, if instead I have an MV field with the single value "red", then I would want it left alone

And third, if I have an MV field with the values "N/A", "N/A", and "N/A", then I would want it left alone.

Only when there's a MV field with both the "N/A" field and a non-N/A  field do I want the N/A fields removed.

[PickleRick]

mvfilter() is indeed the way to go but you need to do a bit more bending over backwards to get it only when you need it.

A run-anywhere example:

| makeresults format=csv data="row
1;2;1;3
1;2;3;4
1;1;1;1
4;3;2;4;5
1;1;2;3
1;4;3;2
3;4;5;2
5;5;5
1;1"
| eval split=split(row,";")
``` This creates a set of example data```
| eval totalcount=mvcount(split)
``` This calculates how many elemets we have```
| eval onecount=mvcount(mvfilter(if(split="1",true(),false())))
``` This count how many ones we have```
| eval filtered=if(onecount>0 AND onecount<totalcount,mvfilter(if(split="1",false(),true())),split)
``` And this filters the ones but only if there was at least one (that's generally not needed) and there is less ones than all values ```

[ITWhisperer · Accepted solution]

| eval filtered=mvfilter(mvfield!="N/A")
| fillnull value="N/A" filtered

[dtaylor]

Your solution works perfectly. I still need to do some wider testing to make sure there's no gaps, but it looks like exactly what I need.....the only issue is....I'm not sure *exactly* what it works. I know what fillnull and eval do, but the way you've used mvfilter confuses me. If you have the time, could you explain in simple terms how your solution works, pelase?

[bowesmana]

The mvfunctions generally take an MV field as an input and then perform an operation on each of the values of the MV, so the solution

| eval filtered=mvfilter(mvfield!="N/A")

is saying

- for each value of the MV field called mvfield match each one against the string "N/A" and if it does NOT match (!="N/A") then return that value to the new field filtered, appending each non-matching value to that new field. That new field will then contain all the values of the original mvfield that did not match the string.

The eval is then finally putting back the "N/A" string to the filtered field so that if ALL values of the original field contained N/A then the new field will have a single N/A value.

If you wanted ALL the N/A instances to be present, then replace the mvfilter line with

| eval filtered=coalesce(mvfilter(mvfield!="N/A"), mvfield)

which if you have N/A 3 times in your original, you will have N/A 3 times in your final result.

[kiran_panchavat]

@dtaylor 

Check this,  if a multivalue field contains both "N/A" and at least one non-"N/A" value. If both conditions are met, it removes "N/A" and returns the remaining values otherwise, it keeps the field unchanged.