match - script and chart function

The first expression in the table below returns 0 for Stockholm because 'Stockholm' is not included in the list of expressions in the match function. It also returns 0 for 'Zurich' because the match comparison is case-sensitive.

You can use match to perform a custom sort for an expression.

By default, columns sort numerically or alphabetically, depending on the data.

To change the order, do the following:

1. Open the Sorting section for your chart in the Properties panel.
2. Turn off auto sorting for the column on which you want to do a custom sort.
3. Deselect Sort numerically and Sort alphabetically.

4. Select Sort by expression, and then enter an expression similar to the following:

   =match( Cities, 'Toronto','Boston','Beijing','Stockholm','zurich')

   The sort order on the Cities column changes.

You can also view the numeric value that is returned.

Load script

You can use match to load a subset of data. For example, you can return a numeric value for an expression in the function. You can then limit the data loaded based on the numeric value. Match returns 0 if there is no match. All expressions that are not matched in this example will therefore return 0 and will be excluded from the data load by the WHERE statement.

In the Data load editor, create a new section called Load, and then add the following:

Transactions:
Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, m, orange
3752, 20180916, 5.75, 1, 5646471, S, blue
3753, 20180922, 125.00, 7, 3036491, l, Black
3754, 20180922, 484.21, 13, 049681, xs, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
];
 
/*
Create new table called Transaction_Buckets
Create new fields called Customer, and Color code - Blue and Black
Load Transactions table.
Match returns 1 for 'Blue', 2 for 'Black'. 
Does not return a value for 'blue' because match is case sensitive.
Only values that returned numeric value greater than 0 
are loaded by WHERE statment into Transactions_Buckets table.
*/
 
Transaction_Buckets:
Load
   customer_id,
   customer_id as [Customer], 
   color_code as [Color Code Blue and Black]
Resident Transactions
Where match(color_code,'Blue','Black') > 0;Copy code to clipboard

Results