pick Script and chart function

Overview

A data set contains product data. You want to assign category labels to products based on the category code.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • ProductID

  • ProductName

  • CategoryCode

Load script

Example:
LOAD * INLINE [
ProductID, ProductName, CategoryCode
1, "Laptop", 1
2, "Headphones", 2
3, "Printer", 3
4, "Smartphone", 1
5, "Tablet", 1
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• ProductID

• ProductName

• CategoryCode

Create the following measure:

• =Pick(CategoryCode, 'Electronics', 'Accessories', 'Office Equipment'), to return a valid expression from the list.

The pick function returns a list of values associated with the corresponding index from the CategoryCode numeric field. For example, the label Electronics is the first parameter in the function and is returned for all products with the CategoryCode of 1.

Overview

A data set contains country and region codes. You want to assign region labels to countries based on the region code.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Country

  • RegionCode

Load script

Example:
LOAD * INLINE [
Country, RegionCode
"USA", 1
"Canada", 1
"Germany", 2
"Japan", 3
"Australia", 4
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Country

• RegionCode

Create the following measure:

• =Pick(RegionCode, 'North America', 'Europe', 'Asia', 'Australia'), to return a valid item from the list.

The pick function returns a list of values associated with the corresponding index from the RegionCode numeric field. For example, the label North America is the first parameter in the function and is returned for all countries with the RegionCode of 1.

Overview

This advanced scenario describes how to effectively use a combination of the pick and match functions to map text matches in-line. In programming terms, this serves a similar purpose to a Case statement. The scenario explores this functionality by mapping country names to region-equivalent names, while also providing a default Region value for any previously unmapped Country names.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Countries.

  • The data table has one field called Country.

Load script

Countries:
LOAD * INLINE [
Country
"USA"
"Canada"
"Germany"
"Japan"
"Australia"
"South Africa"
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Country

• RegionCode

Create the following measure:

• =Pick(1+match(Country,'USA','Canada','Germany','Japan','Australia'), 'Unmapped', 'North America', 'North America', 'Europe', 'Asia', 'Australia')

The match function will return the index match of the comma-separated alternatives supplied, starting from 1 = ‘USA’, 2 = ‘Canada’ and so on. If no match is found, zero is returned. This result is then incremented by 1 and fed into the pick function, such that 1 = default value if no match is found, 2 = ‘USA’, 3 = ‘Canada’ and so on.

This combination of Match and Pick function calls will then decode the matching Country names into an equivalent Region name. Any unmapped Country values will return the default Region value, Unmapped.