--- schema_version: "1" id: 21eb45d1769022773f04b7d066707626ff597c892fa616ccd4aba25252e375aa title: Limit Multivalue Fields collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/analytics/bi_dev_guide_saql/guides/reference/bi-saql-performance-multivalue-fields.md canonical_url: https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-performance-multivalue-fields.html source_site: developer.salesforce.com content_type: documentation content_hash: d4b7cd7558774de18941346bd3cd054362f09e634877895308a69a19d9b7efdb products: - Analytics SAQL Developer Guide - analytics --- # Limit Multivalue Fields Multivalue fields can cause poor performance. The behavior of these fields is undefined for `group-by` and `foreach` statements. If possible, write your query so that the fields are referenced only in filters. **note:** To work with multivalue fields, from Setup, in the Quick Find box, enter `Analytics`, and then select **Settings**. In Settings, click the checkbox for **Enable indexing of multivalue fields in CRM Analytics**. If you don’t select this preference, the `mv_to_string()` function returns only the first value in the field. See [mv_to_string()](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-functions-string-mts.md) for more information. Even with indexing enabled, multivalue fields in multilevel grouping, such as `group by(Year, Region)`, can cause poor performance. Here’s FlightsMV, a sample dataset of flight information. | airline | flight_num | origin | dest | pilot | airplane | flight_classes | flight_attendants | distance | num_passengers | | --------- | ---------- | ------ | ---- | ------ | -------------- | ---------------------- | --------------------------- | -------- | -------------- | | southwest | sw301 | lax | sfo | john | boeing 737-700 | business;economy | mark;sara;kate;maria;martin | 1000 | 100 | | united | u321 | lax | sfo | mark | boeing 737-900 | first;business;economy | jen;sophia;emma;alice | 1000 | 200 | | alaska | as400 | lax | sfo | tim | airbus A320 | business;economy | mark;leila;brad | 1000 | 100 | | delta | d301 | lax | sfo | martin | airbus A321 | business;economy | sarah;maria | 1000 | 100 | | southwest | sw302 | sfo | lax | - |   | business;economy | - | 1000 | 100 | | united | u322 | sfo | lax | john | boeing 737-700 | first;business;economy | sarah;martin | 1000 | 200 | | alaska | as401 | sfo | lax | mark | boeing 737-900 | business;economy | maria;mark;sara;kate;martin | 1000 | 100 | | delta | d302 | sfo | lax | tim | airbus A320 | business;economy | emma;jen;sophia;alice | 1000 | 100 | | southwest | sw303 | lax | jfk | robert | airbus A321 | business;economy | leila;mark;brad | 1000 | 100 | | united | u323 | lax | jfk | maria |   | first;business;economy | - | 1000 | 200 | | alaska | as403 | lax | jfk | mark | boeing 737-700 | business;economy | - | 1000 | 100 | | delta | d303 | lax | jfk | tim | boeing 737-900 | business;economy | maria;sarah | 1000 | 100 | | southwest | sw304 | jfk | lax | robert | airbus A320 | business;economy | martin;sarah | 1000 | 100 | | united | u324 | jfk | lax | - | airbus A321 | first;business;economy | kate;mark;sara;maria;martin | 1000 | 200 | | alaska | as404 | jfk | lax | john |   | business;economy | sophia;jen;emma;alice | 1000 | 100 | | delta | d304 | jfk | lax | mark | boeing 737-700 | business;economy | - | 1000 | 100 | | southwest | sw303 | lax | ord | martin | boeing 737-900 | business;economy | - | 1000 | 100 | | united | u323 | lax | ord | robert | airbus A320 | first;business;economy |   | 1000 | 200 | | alaska | as403 | lax | ord | - | airbus A321 | business;economy | brad;mark;leila | 1000 | 100 | | delta | d303 | lax | ord | john | - | business;economy | sarah;maria | 1000 | 100 | The `flight_attendants` column contains multivalue fields. Let’s write a query to filter on the rows where `maria` is listed as a flight attendant. ```nolang q = load "FlightsMV"; q = filter q by 'flight_attendants' in ["maria"]; q = foreach q generate 'airplane' as 'airplane', 'distance' as 'distance', 'flight_attendants' as 'flight_attendants', 'flight_num' as 'flight_num', 'id' as 'id', 'num_passengers' as 'num_passengers', 'origin' as 'origin', 'pilot' as 'pilot'; ``` | airplane | distance | flight_attendants | flight_num | id | num_passengers | origin | pilot | | -------------- | -------- | ----------------- | ---------- | --- | -------------- | ------ | ------ | | boeing 737-700 | 1000 | kate | sw301 | 1 | 100 | lax | john | | airbus A321 | 1000 | maria | d301 | 4 | 100 | lax | martin | | boeing 737-900 | 1000 | kate | as401 | 7 | 100 | sfo | mark | | boeing 737-900 | 1000 | maria | d303 | 12 | 100 | lax | tim | | airbus A321 | 1000 | kate | u324 | 14 | 200 | jfk | - | | - | 1000 | maria | d303 | 20 | 100 | lax | john | The results display the rows that include `maria`. The `flight_attendants` field displays only one flight attendant name when the field is multivalue. To return all the names, use the `mv_to_string()` function. ```nolang q = load "FlightsMV"; q = filter q by 'flight_attendants' in ["maria"]; q = foreach q generate 'airplane' as 'airplane', 'distance' as 'distance', mv_to_string('flight_attendants') as 'flight_attendants', 'flight_num' as 'flight_num', 'id' as 'id', 'num_passengers' as 'num_passengers', 'origin' as 'origin', 'pilot' as 'pilot'; ``` **note:** When using comparison operators in the filter, use `in` and `not in` to return the correct values. Using `==` and `!=` returns unexpected values when null handling is enabled. See [`Group-by` with Null Values](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/guide/bi-saql-statement-groupby.md) for more information. **See Also** - [Multivalue Field](https://developer.salesforce.com/docs/analytics/bi-dev-guide-saql/concept/bi-saql-multivalue.md)