Date: Thu, 27 Jan 2022 08:46:26 +0000 (GMT) Message-ID: <1497102510.126142.1643273186846@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_126141_1341196081.1643273186846" ------=_Part_126141_1341196081.1643273186846 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 mainta= in a single crop circle in their backyard, as long as it confirms to the to= wn regulations. Below is some data on the size of crop circles in town, wit= h a separate entry for each home. Limits are displayed in the adjacent colu= mns, with the inclusive columns indicating whet= her 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:

=20 =20 =20 =20 =20 =20 =20
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 compar= ing column values:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
=20

While accurate, the above transform does not account for the minIn= clusive 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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
=20

In this case, the IF function tests whether the minimum val= ue is inclusive (values of 10 are allowed). If so, the L= ESSTHANEQUAL function is applied. Otherwise, the LESSTHAN function is applied. For the maximum limit, the following step applies:<= /p>

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20 =20 =20 =20
=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 followi= ng, which is the fine (\$15.00) for each foot above or below th= e limits:

=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 15 'fineDollarsPerFt'
=20

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

=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(tooSmall =3D=3D 'true', (minRadius_ft -= Radius_ft) * fineDollarsPerFt, 0.0) 'fine_Dollars'
=20

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

=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(tooSmall =3D=3D 'true', (minRadius_ft -= Radius_ft) * fineDollarsPerFt, if(tooBig =3D=3D 'true', (Radius_ft - maxRa= dius_ft) * fineDollarsPerFt, '0.0')) 'fine_Dollars'
=20

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
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula fine_Dollars NUMFORMAT(fine_Dollars, '\$###.00')<= /td>=20
=20

Results:

After you delete the columns used in the calculation and move the remain= ing ones, you should end up with a dataset similar to the following:

=20 =20 =20 =20 =20 =20 =20 =20 =20
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 fineDolla= rsPerFt to analyze the resulting fine revenue. Before or afte= r the transform where you set the value for fineDollarsPerFt, = you can insert something like the following:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula minRadius_ft '12.5'
=20

After the step is added, select the last line in the recipe. Then, you c= an see how the values in the fineDollars column have been upda= ted.

------=_Part_126141_1341196081.1643273186846--