Message-ID: <329836922.2055.1571826437650.JavaMail.daemon@e613c95ee270> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2054_2000368554.1571826437650" ------=_Part_2054_2000368554.1571826437650 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html IF Function

IF Function

Contents:

=20

=20
=20
=20

=20
=20

The=20 IF=20 function allows you to build if/then/else conditional logic within yo= ur transforms.

NOTE: The IF function is interchangeable w= ith ternary operators. You should use this function instead of ternary cons= truction.

Basic Usage=

Example:

=20

if(State =3D=3D 'NY','New York, New York!'= ,'some other place')

Output:
If the value in the State=  column is NY, return the value New York, New = York!. Otherwise, the returned valueis some other place= .

Nested IF Example:

You can build IF statements within IF statemen= ts as in the following example, in which the second IF is eval= uated if the first one evaluates to false:

=20

if(State =3D=3D 'NY',0.05,if(State=3D=3D'C= A',0.08,0))

A more detailed nested example is available= below.

Syntax

In the following, if the test expression evaluates to true, the true_expression is executed. Otherwise, the = false_expression is executed.

=20

if(test_expression, true_expression,false_= expression)

=20 =20 =20 =20 =20
Argument Required? Data Type Description
test_expression Y string Expression that is evaluated. Must resolve to true or false
true_expression Y string Expression that is executed if test_expression is true
false_expression N string Expression that is executed = if test_expression is false

All of these expressions can be constants (strings, integers, or any oth= er supported literal value) or sophisticated elements of logic, although th= e test expression must evaluate to a Boolean value.

For more information on syntax standards, see Language Documentation Syntax Notes<= /a>.

test_expression

This parameter contains the expression to evaluate. This expression must= resolve to a Boolean (true or false) value.

Usage Notes:

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (expression that evaluates to true<= /code> or false (LastName =3D=3D 'Mouse' && FirstN= ame =3D=3D 'Mickey')

true_expression, fal= se_expression

The true_expression determines the value or conditional tha= t is generated if the test_expression evaluates to true<= /code>. If the test is false, then the false_expression<= /code> applies.

These expressions typically generate output values and can use a combina= tion of literals, functions, and column references.

• A true expression is required. You can insert a blank expression ( "").
• If a false expression is not provided, false results yield a value of <= code>false.

Usage Notes:

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (expression)

See examples below.

Examples

Example - Basic Usage

Example data:

X Y
true true
true false
false true
false false

Transformation:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula if((X =3D=3D Y), 'yes','no') 'equals'
=20

Results:

Your output looks like the following:

=20 =20 =20 =20
X Y equals
true true yes
true false no
false true no
false false yes

Example - Stock Quotes

This example demonstrates how you can chain together multiple if/then/el= se conditions within a single transform step.

You have a set of stock prices that you want to analyze. Based on a set = of rules, you want to determine any buy, sell, or hold action to take.

Source:

Ticket Qty BuyPrice CurrentPrice
GOOG 10 705.25 674.5
FB 100 84.00 101.125
AAPL 50 125.25 97.375
MSFT 100 38.875 45.25

Transformation:

You can perform evaluations of this data using the IF funct= ion to determine if you want to take action.

To assist in evaluation, you might first want to create columns that con= tain the cost (Basis) and the current value (CurrentValu= e) for each stock:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula (Qty * BuyPrice) 'Basis'
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula (Qty * CurrentPrice) 'CurrentValue'
=20

Now, you can build some rules based on the spread between Basis and CurrentValue.

Single IF version: In this case, the most = important action is determining if it is time to sell. The following rule w= rites a sell notification if the current value is \$1000 or mor= e than the cost. Otherwise, no value is written to the action column.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula if((CurrentValue - 1000 > Basis), 'sell= ','') 'action'
=20

Nested IF version: But = what about buying more? The following transform is an edit to the previous = one. In this new version, the sell test is performed, and if false, writes a buy action if the CurrentPrice is = within 10% of the BuyPrice.

This second evaluation is performed after the first one, as it replaces = the else clause, which did nothing in the previous version. In the Recipe P= anel, click the previous transform and edit it, replacing it with the new v= ersion:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula if((CurrentValue - 1000 > Basis), 'sell= ', if((abs(CurrentValue - Basis) <=3D (Basis * 0.1)),'buy','hold')) 'action'
=20

If neither test evaluates to true, the written action is hold.

You might want to format some of your columns using dollar formatting, a= s in the following:

NOTE: The following formatting inserts a dollar sign (\$= ) in front of the value, which changes the data type to String.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula BuyPrice numformat(BuyPrice, '\$ ##,###.00')<= /td>=20
=20

Results:

After moving your columns, your dataset should look like the following, = if you completed the number formatting steps:

=20 =20 =20 =20 =20 =20 =20 =20
Ticket Qty BuyPrice CurrentPrice Basis CurrentValue action
GOOG 10 705.25 \$ 674.50 \$ 7,052.50 \$ 6,745.00 buy
FB 100 84.00 \$ 101.13 \$ 8,400.00 \$ 10,112.50 sell
AAPL 50 125.25 \$ 97.38 \$ 6,262.50 \$ 4,868.75 hold
MSFT 100 38.88 \$ 45.25 \$ 3,887.50 \$ 4,525.00 hold

=20