String Functions - Tableau

This document is prepared intend to give notes on Srting Calculations.
Some times we get the need the of String calculations in the projects like concatenating First Name & Last Name, Converting Lower case to Upper Case or keep string in perfect (As per XL function)......
The following are the list of String functions. Click on each Blue color link, which taks to you that related dashboard & it gives in deapth information abut that.

Calculations:
We are using Calculations to get the Customized results, which are directly not available in the Databse.
EX: Conditional Formatting, Customized format

3 Types
String, Date, Numerical

String Functions - Tableau

1)    ASCII

This function returns first letter of given String's ASCII value and output of this function is Integer.

EX 1: ASCII(“Tableau”) =  84

Ascii value of [Customer Name], It means First Letter of [Customer Name] 's ASCII value.

2)     CHAR:

This function converts numerical number in to character.

EX 1: char(65)= A

EX 2: Is converting given input Numerical value into Character as per ASCII.

3)    CONTAINS:

It will check whether substring is available or not in the main string. It means it gives the Outputs as True/False

EX:  [Region] = South (Here [Region] is the Field and South is the Member of Region field)

contains([Region], "st") = False

contains([Region], "so") = True

contains([Region], "So") =True

EX 2: Verifying whether Regions are having given sub strings, it means If Region has the substring "st" and "s" then gives "True" else "False"

contains([Region], "st") and contains([Region], "s")

4)    ENDSWITH:

It will check whether substring is endswith or not in the main string. It means it gives the Outputs as True/False.

EX 1: [Region] = South (Here [Region] is the Field and South is the Member of Region field)

ENDSWITH( [Region], 'st' ) = False

1) endwith([Region], "th") = True

2) endwith([Region], "k") = False

EX 2: 

     Want to show Regions in the report which are ends with "st" sub string Verifies the Region Names with specified Condition that is ends with "st", if region ends with "st" then returns "True" else "False"

Steps: 

1) Created a Calculated Field with the following Syntax.

Name: ENDSWITH

Syntax: ENDSWITH( [Region], 'st' )

2) Then Dragged [Region] into Work Area, [ENDSWITH] into Filters shelf, then selected "True".

5)    FIND:

It will check the position of substring in the main string. It means it gives the Outputs as Numerical Numbers.

EX 1:

Find ([Region], "th") = 4

Find ([Region], "k") = 0

EX 2: Find where is located " " (Space) in the Customer Names

Steps: 

1) Find Create a calculated field like below

Name: FIND

Syntax: Find([Customer Name], " ")

2) Drag the [Customer Name], [FIND] into work area.

     3) Then Report will show the [Customer Name] and position where located Space in the Customer Names.

Note: 

     Out put of this function is INTEGER, so it will take Sum as the Default aggregate function & gives wrong output, here we can overcome it by changing the aggregate function to Average.

6)     ISDATE:

This is used to test the given string data is Date or not, if given data is date then returns “True” else "False".

EX:  isdate(str([Order_Date])) =  True

        Isdate(’12-Feb-2011’) = True

EX 2: Verifying few given string data is Date or not.

1) isdate(str([Ship Date]))

2) isdate('Feb-25-2013')

3) Isdate("abcd-12-2014")

7)    LEFT:

We use this function to fetch substring from starting point to specific point in the given Main String.

EX 1: Left([Customer Name],3) = Sou

Region = South

Left([Region],4) = Sout

Ex 2: Scenario: Want to extract first 3 Characters from [Customer Name] field.

1) Create a Calculated Field like Below

Name: Left 3

Syntax: Left([Customer Name],3)

2)  Drag  [Customer Name], [Left 3] into Work Area

3) View will show the Customer Name, First 3 Letters of the Customer Name

8)    LTRIM

We use this function to remove the speaces left side of the given string.

And this function does not remove spaces in the mid of the given string.

EX:

ltrim(" ASDF")

O/P: ASDF

9)    LEN

This function count the number of characters in the given string including nulls, Spaces & returns Numerical number.

EX: [Region] = South (Here [Region] is the Field and South is the Member of Region field)

Len([Region]) = 5 ( South has 5 Characters)

10)                       Usage of this Function: MIN, MAX

MAX: Test the given multiple string data& returns Maximum as output.

Max(“A”, “B”)= B

Max(“AAAA”, “D”)= D

Min:

This function verifies given multiple string data & it gives minimum number as output as per first character’s ASCII value.

EX: Min ('A', 'B') = A

11)                       UPPER:

This function converts the given string into lower Case.

Similarly, Upper Function converts given string into UPPER Case.

12)                       MID

This function is used to extract the substring from specific point to specific point.

EX: Region = South

Mid( [Region], 2,4) = out

EX 2:: Extract the first 4 Characters in the Customers Names.

nt. 1) Create a calculated field like below syntax.

Name: Mid

Syntax: mid([Customer Name], 1,4)

2) Then drag the [Customer Name], [Mid] field into work area.

3) Now view will show Customer Name, first 4 Characters in the Customers Names.

13)                       REPLACE

This function is used to replace the substring by other substring.

EX:

[Region] = East, West, North, Central

REPLACE([Region], "st", "ASDF")

O/P: EaASDF, WeASDF,  North, Central

Ex 2: Want to replace "Kay" by "AAAAA" in the Customer Names

1) Create a Calculated Field like below

Name: Replace

Syntax: REPLACE( [Customer Name], "Kay", "AAAAA")

2) Drag the Customer Name, Replace into Work Area.

14)                       RIGHT:

We use this function to fetch substring from ending point to specific point in the given Main String.

EX: Right([Customer Name],3)

Region = South

Right([Region],2) = th

This View: Scenario: Want to extract Latst 2 Characters from [Customer Name] field.

1) Create a Calculated Field like Below.

Name: Right 2

Syntax: Right([Customer Name],2)

2)  Drag  [Customer Name], [Right 2] into Work Area

3) View will show the Customer Name, Right 2 Letters of the Customer Name

15)                       STARTSWITH:

This function test whether given string starts with specified substring, & returns Boolean.

EX:

[Region] = South (Here [Region] is the Field and South is the Member of Region field)

STARTSWITH([Region], "W") = False

STARTSWITH([Region], "Su") = False

STARTSWITH([Region], "S") = True

STARTSWITH([Region], "So") = True