How to combine field values into singular value?

[michaeler]

I feel like there's a simple solution to this that I just can't remember. I have a field named Domain that has 13 values and I want to combine ones that are similar into single field values. This is how it currently looks:

Domain:                                                                           Count:
BC                                                                                      1
WIC                                                                                    3
WIC, BC                                                                            2
WIC, UPnet                                                                    3
WIC, DWnet                                                                   5
WIC, DWnet, BC                                                           6
WIC, DWnet, UPnet                                                    1
WIC/UPnet                                                                    3
WIC/DWnet                                                                   2
UPnet                                                                              5
UPnet, SG                                                                       6
DWnet                                                                              1
DW                                                                                     1

I want to merge the values "WIC, UPnet" and "WIC/UPnet" to "WIC,UPnet" | "WIC, DWnet" and WIC/DWnet" to "WIC, DWnet" | "DWnet" and "DW" to "DWnet"

New results should read:

Domain:                                                                           Count:
BC                                                                                      1
WIC                                                                                    3
WIC, BC                                                                            2
WIC, UPnet                                                                    6
WIC, DWnet                                                                   7
WIC, DWnet, BC                                                           6
WIC, DWnet, UPnet                                                    1
UPnet                                                                              5
UPnet, SG                                                                       6
DWnet                                                                              2

[michaeler]

Might have solved it myself:

| eval Domain=if(domain="DWnet" OR domain="DW", "DWnet", domain)

That seemed to work. If someone else has a better alternative, I'm open to suggestions

[bowesmana]

Unless the "rule" you want is based on a regex, which given the DW->DWnet requirement, it seems unlikely, then as you have found, an eval to create the desired domain name is simplest, but you can use a case statement

| eval domain=case(domain="DW", "DWnet", 
                   domain="WIC/UPnet", "WIC, UPnet",
                   domain="WIC/DWnet", "WIC, DWnet"
                   true(), domain)

or if you have a lot of these, then a different solution is to create a lookup with all the possible variants and the form you want to see in the form

domain_in, domain_out
DW,DWnet
WIC/UPnet,"WIC, UPnet"
WIC/DWnet,"WIC, DWnet"

 and then in the SPL do

| lookup my_lookup domain_in as domain OUTPUT domain_out
| eval domain=coalesce(domain_out, domain)

which means you only have to put the different variants in the lookup, as the coalesce will then take the found form in the lookup (domain_out) and use that if present. Note that if using a CSV, make sure you use quotes for the values that have commas in them