## 一般規則：避免在另一個計算中使用某個計算欄位多次

### 範例

`RIGHT([Twitter Handle], LEN([Twitter Handle]) -1)`

`MID([Twitter Handle], 2)`

## 提示 1：將多個等式比較轉換為 CASE 運算式或組

`IF [Person (calc)] = 'Henry Wilson'OR [Person (calc)] = 'Jane Johnson'OR [Person (calc)] = 'Michelle Kim'OR [Person (calc)] = 'Fred Suzuki'OR [Person (calc)] = 'Alan Wang'THEN 'Lead'ELSEIF [Person (calc)] = 'Susan Nguyen'OR [Person (calc)] = 'Laura Rodriguez'OR [Person (calc)] = 'Ashley Garcia'OR [Person (calc)] = 'Andrew Smith'OR [Person (calc)] = 'Adam Davis'THEN 'IC'END`

### 解決方案 1

`CASE [Person (calc)]WHEN 'Henry Wilson' THEN 'Lead'WHEN 'Jane Johnson' THEN 'Lead'WHEN 'Michelle Kim' THEN 'Lead'WHEN 'Fred Suzuki' THEN 'Lead'WHEN 'Alan Wang' THEN 'Lead'WHEN 'Susan Nguyen' THEN 'IC'WHEN 'Laura Rodriguez' THEN 'IC'WHEN 'Ashley Garcia' THEN 'IC'WHEN 'Andrew Smith' THEN 'IC'WHEN 'Adam Davis' THEN 'IC'END`

## 提示 2：將多個字串計算轉換為單一 REGEXP 運算式

### 範例 1：CONTAINS

`IF CONTAINS([Segment (calc)],'UNKNOWN')OR CONTAINS([Segment (calc)],'LEADER')OR CONTAINS([Segment (calc)],'ADVERTISING')OR CONTAINS([Segment (calc)],'CLOSED')OR CONTAINS([Segment (calc)],'COMPETITOR')OR CONTAINS([Segment (calc)],'REPEAT')THEN 'UNKNOWN'ELSE [Segment (calc)] END`

#### 解決方案

`IF REGEXP_MATCH([Segment (calc)], 'UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT') THEN 'UNKNOWN'ELSE [Segment (calc)] END`

### 範例 2：STARTSWITH

`IF STARTSWITH([Segment (calc)],'UNKNOWN')OR STARTSWITH([Segment (calc)],'LEADER')OR STARTSWITH([Segment (calc)],'ADVERTISING')OR STARTSWITH([Segment (calc)],'CLOSED')OR STARTSWITH([Segment (calc)],'COMPETITOR')OR STARTSWITH([Segment (calc)],'REPEAT')THEN 'UNKNOWN'`

#### 解決方案

`IF REGEXP_MATCH([Segment (calc)], '^(UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT)') THEN 'UNKNOWN'ELSE [Segment (calc)] END`

### 範例 3：ENDSWITH

`IF ENDSWITH([Segment (calc)],'UNKNOWN')OR ENDSWITH([Segment (calc)],'LEADER')OR ENDSWITH([Segment (calc)],'ADVERTISING')OR ENDSWITH([Segment (calc)],'CLOSED')OR ENDSWITH([Segment (calc)],'COMPETITOR')OR ENDSWITH([Segment (calc)],'REPEAT')THEN 'UNKNOWN'ELSE [Segment (calc)] END`

#### 解決方案

`IF REGEXP_MATCH([Segment (calc)], '(UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT)\$') THEN 'UNKNOWN'ELSE [Segment (calc)] END`

## 提示 3：使用 REGEXP（而不是 LEFT、MID、RIGHT、FIND、LEN）操作字串

### 範例 1

`IF (STARTSWITH([Server], "http://")) THEN MID([Server], Len("http://") + 1)ELSEIF(STARTSWITH([Server], "https://")) THEN MID([Server], Len("https://") + 1)ELSEIF(STARTSWITH([Server], "tcp:")) THEN MID([Server], Len("tcp:") + 1)ELSEIF(STARTSWITH([Server], "\\")) THEN MID([Server], Len("\\") + 1)ELSE [Server]END`

#### 解決方案

`REGEXP_REPLACE([Server], "^(http://|https://|tcp:|\\\\)", "")`

### 範例 2

`IF (FINDNTH([Server], ".", 2) > 0) THENMID([Server],FIND([Server], ".") + 1,FINDNTH([Server], ".", 2) - FINDNTH([Server], ".", 1) - 1)END`

#### 解決方案

`REGEXP_EXTRACT([Server], "\.([^\.]*)\.")`

## 提示 4：不要在計算中使用集

### 範例

`IF ISNULL([Customer Name]) OR [Top customers (set)] THEN [Segment] ELSE [Customer Name] END`

### 解決方案 1

`CASE [Customer Name]WHEN 'Henry Wilson' THEN TrueWHEN 'Jane Johnson' THEN TrueWHEN 'Michelle Kim' THEN TrueWHEN 'Fred Suzuki' THEN TrueWHEN 'Alan Wang' THEN TrueELSE FalseEND`

### 解決方案 2

`IF ISNULL([Customer Name]) OR [Top Customers(group)]='IN' THEN [Segment] ELSE [Customer Name] END`

## 提示 5：不要使用集對資料進行分組

`IF [Americas Set] THEN "Americas"ELSEIF [Africa Set] THEN "Africa"ELSEIF [Asia Set] THEN "Asia"ELSEIF [Europe Set] THEN "Europe"ELSEIF [Oceania Set] THEN "Oceania"ELSE "Unknown"END`

• 集並不始終是排他的。某些成員可能會出現在多個集中。例如， [俄羅斯] 既可能放在 [歐洲] 集中，也可能放在 [亞洲] 集中。

• 集並不會始終轉換為群組。如果集是透過排除、條件或限制定義的，則可能很難甚至無法建立等效的組。

Tableau 中的計算類型

Tableau 中的函數