Excerpt 

This example demonstrates functions for comparing the relative values of two functions. 
Functions:
D generate list excerpts 

pages  LESSTHANEQUAL Function,LESSTHAN Function,GREATERTHANEQUAL Function, GREATERTHAN Function 


In the town of Circleville, citizens are allowed to maintain a single crop circle in their backyard, as long as it confirms to the town regulations. Below is some data on the size of crop circles in town, with a separate entry for each home. Limits are displayed in the adjacent columns, with the inclusive
columns indicating whether the minimum or maximum values are inclusive.
Tip 

Tip: As part of this exercise, you can see how to you can extend your recipe to perform some simple financial analysis of the data. 
Source:
Location  Radius_ft  minRadius_ft  minInclusive  maxRadius_ft  maxInclusive 

House1  55.5  10  Y  25  N 
House2  12  10  Y  25  N 
House3  14.25  10  Y  25  N 
House4  3.5  10  Y  25  N 
House5  27  10  Y  25  N 
Transformation:
After the data is loaded into the Transformer page, you can begin comparing column values:
D trans 

RawWrangle  true 

p03Value  'tooSmall' 

Type  step 

WrangleText  derive type:single value: LESSTHANEQUAL(Radius_ft,minRadius_ft) as:'tooSmall' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  LESSTHANEQUAL(Radius_ft,minRadius_ft) 

p03Name  New column name 

SearchTerm  New formula 


While accurate, the above transform does not account for the minInclusive
value, which may be changed as part of your steps. Instead, you can delete the previous transform and use the following, which factors in the other column:
D trans 

RawWrangle  true 

p03Value  'tooSmall' 

Type  step 

WrangleText  derive type:single value: IF(minInclusive == 'Y',LESSTHANEQUAL(Radius_ft,minRadius_ft),LESSTHAN(Radius_ft,minRadius_ft)) as:'tooSmall' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  IF(minInclusive == 'Y',LESSTHANEQUAL(Radius_ft,minRadius_ft),LESSTHAN(Radius_ft,minRadius_ft)) 

p03Name  New column name 

SearchTerm  New formula 


In this case, the IF
function tests whether the minimum value is inclusive (values of 10
are allowed). If so, the LESSTHANEQUAL
function is applied. Otherwise, the LESSTHAN
function is applied. For the maximum limit, the following step applies:
D trans 

RawWrangle  true 

p03Value  'tooBig' 

Type  step 

WrangleText  derive type:single value: IF(maxInclusive == 'Y', GREATERTHANEQUAL(Radius_ft,maxRadius_ft),GREATERTHAN(Radius_ft,maxRadius_ft)) as:'tooBig' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  IF(maxInclusive == 'Y', GREATERTHANEQUAL(Radius_ft,maxRadius_ft),GREATERTHAN(Radius_ft,maxRadius_ft)) 

p03Name  New column name 

SearchTerm  New formula 


Now, you can do some analysis of this data. First, you can insert a column containing the amount of the fine per foot above the maximum or below the minimum. Before the first derive
command, insert the following, which is the fine ($15.00
) for each foot above or below the limits:
D trans 

RawWrangle  true 

p03Value  'fineDollarsPerFt' 

Type  step 

WrangleText  derive type:single value: 15 as:'fineDollarsPerFt' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  15 

p03Name  New column name 

SearchTerm  New formula 


At the end of the recipe, add the following new line, which calculates the fine for crop circles that are too small:
D trans 

RawWrangle  true 

p03Value  'fine_Dollars' 

Type  step 

WrangleText  derive type:single value: IF(tooSmall == 'true', (minRadius_ft  Radius_ft) * fineDollarsPerFt, 0.0) as: 'fine_Dollars' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  IF(tooSmall == 'true', (minRadius_ft  Radius_ft) * fineDollarsPerFt, 0.0) 

p03Name  New column name 

SearchTerm  New formula 


The above captures the toosmall violations. To also capture the toobig violations, change the above to the following:
D trans 

RawWrangle  true 

p03Value  'fine_Dollars' 

Type  step 

WrangleText  derive type:single value: IF(tooSmall == 'true', (minRadius_ft  Radius_ft) * fineDollarsPerFt, if(tooBig == 'true', (Radius_ft  maxRadius_ft) * fineDollarsPerFt, '0.0')) as: 'fine_Dollars' 

p01Name  Formula type 

p01Value  Single row formula 

p02Name  Formula 

p02Value  IF(tooSmall == 'true', (minRadius_ft  Radius_ft) * fineDollarsPerFt, if(tooBig == 'true', (Radius_ft  maxRadius_ft) * fineDollarsPerFt, '0.0')) 

p03Name  New column name 

SearchTerm  New formula 


In place of the original "false" expression (0.0
), the above adds the test for the toobig values, so that all fines are included in a single column. You can reformat the fine_Dollars
column to be in dollar format:
D trans 

RawWrangle  true 

Type  step 

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

p01Name  Columns 

p01Value  fine_Dollars 

p02Name  Formula 

p02Value  NUMFORMAT(fine_Dollars, '$###.00') 

SearchTerm  Edit column with formula 


Results:
After you delete the columns used in the calculation and move the remaining ones, you should end up with a dataset similar to the following:
Location  fineDollarsPerFt  Radius_ft  minRadius_ft  minInclusive  maxRadius_ft  maxInclusive  fineDollars 

House1  15  55.5  10  Y  25  N  $457.50 
House2  15  12  10  Y  25  N  $0.00 
House3  15  14.25  10  Y  25  N  $0.00 
House4  15  3.5  10  Y  25  N  $97.50 
House5  15  27  10  Y  25  N  $30.00 
Now that you have created all of the computations for generating these values, you can change values for minRadius_ft
, maxRadius_ft
, and fineDollarsPerFt
to analyze the resulting fine revenue. Before or after the transform where you set the value for fineDollarsPerFt
, you can insert something like the following:
D trans 

RawWrangle  true 

Type  step 

WrangleText  set col: minRadius_ft value:'12.5' 

p01Name  Columns 

p01Value  minRadius_ft 

p02Name  Formula 

p02Value  '12.5' 

SearchTerm  Edit column with formula 


After the step is added, select the last line in the recipe. Then, you can see how the values in the fineDollars
column have been updated.
D s also 

label  example_comparison_functions2 

