Page tree

 

Support | BlogContact Us | 844.332.2821

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

D toc
Excerpt

The IF function allows you to build if/then/else conditional logic within your transforms.

Info

NOTE: The IF function is interchangeable with ternary operators. You should use this function instead of ternary construction.

D s
snippetBasic

Example:

D code

derive value:IF(State == 'NY','New York, New York!','some other place') as:'isNewYork'

Output: Generates a new isNewYork column, in which each row contains the value New York, New York! when the corresponding value in the State column is NY. Otherwise, the value in isNewYork is some other place.

Nested IF Example: 

You can build IF statements within IF statements as in the following example, in which the second IF is evaluated if the first one evaluates to false:

D code

derive value:IF(State == 'NY',0.05,IF(State=='CA',0.08,0)) as:'CoTaxRatesByState'

A more detailed nested example is available below.

D s
snippetSyntax

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

Code Block
IF(test_expression, true_expression,false_expression)
ArgumentRequired?Data TypeDescription
test_expressionYstringExpression that is evaluated. Must resolve to true or false
true_expressionYstringExpression that is executed if test_expression is true
false_expressionNstringExpression that is executed if test_expression is false

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

d-s-lang-notes

test_expression

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

D s
snippetusage

Required?Data TypeExample Value
YesString (expression that evaluates to true or false(LastName == 'Mouse' && FirstName == 'Mickey')

true_expression, false_expression

The true_expression determines the value or conditional that is generated if the test_expression evaluates to true. If the test is false, then the false_expression applies.

These expressions typically generate output values and can use a combination 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 false.

D s
snippetusage

Required?Data TypeExample Value
YesString (expression)

See examples below.

D s
snippetExamples

Example - Basic Usage

Example data:

XY
truetrue
truefalse
falsetrue
falsefalse

Transforms:

D code

derive value:IF((X == Y), 'yes','no') as: 'equals'

Results:

Your output looks like the following:

XYequals
truetrueyes
truefalseno
falsetrueno
falsefalseyes

Example - Stock Quotes

This example demonstrates how you can chain together multiple if/then/else 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:

TicketQtyBuyPriceCurrentPrice
GOOG10705.25674.5
FB10084.00101.125
AAPL50125.2597.375
MSFT10038.87545.25

Transform:

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

Info

NOTE: For a larger dataset, you might maintain your buy, sell, and hold evaluations for each stock in a separate reference dataset that you join to the source dataset before performing comparisons between column values. See Join Page.

To assist in evaluation, you might first want to create columns that contain the cost (Basis) and the current value (CurrentValue) for each stock:

D code

derive value:(Qty * BuyPrice) as:'Basis'

D code

derive value:(Qty * CurrentPrice) as:'CurrentValue'

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 writes a sell notification if the current value is $1000 or more than the cost. Otherwise, no value is written to the action column.

D code

derive value: IF((CurrentValue - 1000 > Basis), 'sell','') as:'action'

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 Panel, click the previous transform and edit it, replacing it with the new version:

D code

derive value: IF((CurrentValue - 1000 > Basis), 'sell', IF((abs(CurrentValue - Basis) <= (Basis * 0.1)),'buy','hold')) as:'action'

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

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

Info

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

D code

set col:BuyPrice value:NUMFORMAT(BuyPrice, '$ ##,###.00')

Results:

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

TicketQtyBuyPriceCurrentPriceBasisCurrentValueaction
GOOG10705.25$ 674.50$ 7,052.50$ 6,745.00buy
FB10084.00$ 101.13$ 8,400.00$ 10,112.50sell
AAPL50125.25$ 97.38$ 6,262.50$ 4,868.75hold
MSFT10038.88$ 45.25$ 3,887.50$ 4,525.00hold

D s also
labelother