Date: Mon, 18 Oct 2021 03:22:45 +0000 (GMT) Message-ID: <1871532425.47178.1634527365312@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_47177_346583797.1634527365312" ------=_Part_47177_346583797.1634527365312 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - Comparison Functions2

# EXAMPLE - Comparison Functions2

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 s= ize of crop circles in town, with a separate entry for each home. Limits ar= e displayed in the adjacent columns, with the `inclusive`= columns indicating whether the minimum or maximum values are inclusive.

Tip: As part of this exercise, you can see how to you c= an extend your recipe to perform some simple financial analysis of the data= .

Source:

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

Transform:

After the data is loaded into the Transformer page, you can begin compar= ing column values:

=20

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, whic= h factors in the other column:

=20

In this case, the `IF` function tests whether the minimu= m value is inclusive (values of `10` are allowed). If so, t= he `LESSTHANEQUAL` function is applied. Otherwise, the LESSTHAN function is applied. For the maximum limit, the following= step applies:

=20

Now, you can do some analysis of this data. First, you can insert a colu= mn containing the amount of the fine per foot above the maximum or below th= e minimum. Before the first `derive` command, insert the fo= llowing, which is the fine (`\$15.00`) for each foot above or bel= ow the limits:

=20

derive type:single value: 15 as:'fin= eDollarsPerFt'

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

=20

derive type:single value: IF(tooSmal= l =3D=3D 'true', (minRadius_ft - Radius_ft) * fineDollarsPerFt= , 0.0) as: 'fine_Dollars'

The above captures the too-small violations. To also capture the too-big= violations, change the above to the following:

=20

derive type:single value: IF(t= ooSmall =3D=3D 'true', (minRadius_ft - Radius_ft) * fineDollarsPerFt, if(to= oBig =3D=3D 'true', (Radius_ft - maxRadius_ft) * fineDollarsPerFt, '0.0')) as: 'fine_Dollars'

In place of the original "false" expression (`0.0`), the abov= e adds the test for the too-big values, so that all fines are included in a= single column. You can reformat the `fine_Dollars` column to be in dollar format:

=20

set col: fine_Dollars value: NUMFORMAT(fine_Dolla= rs, '\$###.00')

Results:

After you drop the columns used in the calculation and move the remainin= g ones, you should end up with a dataset similar to the following:

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 t= hese values, you can change values for `minRadius_ft`, `maxRadius_ft`, and ```fineDollarsPer= Ft``` to analyze the resulting fine revenue. Before or after the = transform where you set the value for `fineDollarsPerFt`, y= ou can insert something like the following:

=20

After the step is added, select the last line in the recipe. Then,= you can see how the values in the `fineDollars` column hav= e been updated.