Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

...

D s
snippetBasic

Example:

IF
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
if(State == 'NY','New York, New York!','some other place') as:'isNewYork'

...

if(State == 'NY','New York, New York!

...

','some other place')


Output:
If the value in the the State column is NY column is NY, return the value New York, New York!. Otherwise, the value in isNewYork is returned valueis 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:

IFIF
d-codelang-syntax
RawWrangletrue
Typeref
showNotetrue
WrangleTextderive type:single value:
if(State == 'NY',0.05,
if(State=='CA',0.08,0)) as:'CoTaxRatesByState'

if(State == 'NY',0.05,if(State=='CA',0.08,0))

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.

IF
Code Block
D lang syntax
RawWrangletrue
Typesyntax
showNotetrue
WrangleTextif(test_expression,
true_expression,false_expression)

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

...

XY
truetrue
truefalse
falsetrue
falsefalse

TransformsTransformation:

d-

...

trans
RawWrangletrue
p03Value'equals'
Typestep
WrangleTextderive type:single value:

...

if((X == Y), 'yes','no') as: 'equals'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueif((X == Y), 'yes','no')
p03NameNew column name
SearchTermNew formula

Results:

Your output looks like the following:

...

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

TransformTransformation:

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

...

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-

...

trans
RawWrangletrue
p03Value'Basis'
Typestep
WrangleTextderive type:single value:(Qty * BuyPrice) as:'Basis'

...

p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(Qty * BuyPrice)
p03NameNew column name
SearchTermNew formula

D trans
RawWrangletrue
p03Value'CurrentValue'
Typestep
WrangleTextderive type:single value:(Qty * CurrentPrice) as:'CurrentValue'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Value(Qty * CurrentPrice)
p03NameNew column name
SearchTermNew formula

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-

...

trans
RawWrangletrue
p03Value'action'
Typestep
WrangleTextderive type:single value:

...

if((CurrentValue - 1000 > Basis), 'sell','') as:'action'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueif((CurrentValue - 1000 > Basis), 'sell','')
p03NameNew column name
SearchTermNew formula

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-

...

trans
RawWrangletrue
p03Value'action'
Typestep
WrangleTextderive type:single value:

...

if((CurrentValue - 1000 > Basis), 'sell',

...

if((abs(CurrentValue - Basis) <= (Basis * 0.1)),'buy','hold')) as:'action'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueif((CurrentValue - 1000 > Basis), 'sell', if((abs(CurrentValue - Basis) <= (Basis * 0.1)),'buy','hold'))
p03NameNew column name
SearchTermNew formula

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

...

Info

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

d-

...

trans
RawWrangletrue
Typestep
WrangleTextset col:BuyPrice value:

...

numformat(BuyPrice, '$ ##,###.00')
p01NameColumns
p01ValueBuyPrice
p02NameFormula
p02Valuenumformat(BuyPrice, '$ ##,###.00')
SearchTermEdit column with formula

Results:

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

...