Nested IF statements using DataColumn.Expression Property in .NET

Question

I have a requirement where I'm required to use the DataColumn.Expression Property syntax (link provided below) to essentially run some pretty complex equations, including some polynomial equations that if I don't put in some mix/max limits on them will go in some extremes we don't want. Essentially I have many nested IIF statements while trying to keep the polynomial equation going no higher than 200 or less than 70 all while moving into new temp value ranges. It would be nice if there is some type of min/max functions I could use within the .NET expression property syntax but I could find anything that would work for this. I know my syntax below isn't right either since its going to zero for the false side if the first expression fails if I'm reading this right. Any help or direction would be appreciated. Please let me know if need to explain any of this better. It's really hard to find any expanded resources on this syntax anywhere beyond this link below.

MSDN Data Column Expression

IIF([temp]>=0 and [temp]<20,
IIF((23+([duration]*6))-(0.4779*[temp])+(0.06755*([temp]^2))-(0.002237*([temp]^3))+(0.0001321*([temp]^4))<70, 70,
IIF((23+([duration]*6))-(0.4779*[temp])+(0.06755*([temp]^2))-(0.002237*([temp]^3))+(0.0001321*([temp]^4))>200, 200,
IIF([temp]>=0 and [temp]<20,
23+([duration]*6))-(0.4779*[temp])+(0.06755*([temp]^2))-(0.002237*([temp]^3))+(0.0001321*([temp]^4),
IIF([temp]>=0 and [temp]<25, 60,
IIF([temp]>=0 and [temp]<30, 50,
IIF([temp]>=0 and [temp]<35, 0,
IIF([temp]>=20,
IIF((33+([duration]*6))-(0.3779*[temp])+(0.04758*([temp]^2))-(0.0017*([temp]^3))+(0.000151*([temp]^4))<70, 70,
IIF((33+([duration]*6))-(0.3779*[temp])+(0.04758*([temp]^2))-(0.0017*([temp]^3))+(0.000151*([temp]^4))>200, 200,
IIF([temp]>=20,
33+([duration]*6))-(0.3779*[temp])+(0.04758*([temp]^2))-(0.0017*([temp]^3))+(0.000151*([temp]^4),
IIF([temp]>=0 and [temp]<25, 60,
IIF([temp]>=0 and [temp]<30, 50,
IIF([temp]>=0 and [temp]<35, 0,)))))), 0))))))), 0)

Answer 1

Deeply nested IIF() statements are painful to deal with in any environment and yours is currently 12-13 levels deep.

But don't despair -- in this case you can break the problem down into smaller pieces using multiple calculated DataColumns.

Calculated DataColumns can reference other calculated DataColumns. You can leverage this to make the calculations easier to write (and read) and also to reduce the amount of repeated code in your expressions -- especially the 'ultimate' expression you want to calculate.

Exponents / Powers

First problem is your use of the 'power' operator (the caret '^' symbol). This operator is not supported in DataColumn Expressions. You need an alternative.

So here we get our first chance to add some calculated columns for the 'power' values of your datapoint [temp].

var dt = new DataTable();
dt.Columns.Add("temp", typeof(int));
dt.Columns.Add("duration", typeof(int));

// [temp]^2
dt.Columns.Add("tempSquared", typeof(int));
dt.Columns["tempSquared"].Expression = "[temp]*[temp]";

// [temp]^3
dt.Columns.Add("tempCubed", typeof(int));
dt.Columns["tempCubed"].Expression = "[temp]*[temp]*[temp]";

// [temp]^4
dt.Columns.Add("tempToTheFourth", typeof(int));
dt.Columns["tempToTheFourth"].Expression = "[temp]*[temp]*[temp]*[temp]";

Primary Calculations

These calculated columns can now be referenced by additional calculated columns. This means you can add each of your two primary calculations by referencing the above 'power' value columns (to get around missing power operator problem).

dt.Columns.Add("calc23", typeof(float));
dt.Columns["calc23"].Expression = "(23+([duration]*6))-(0.4779*[temp])+(0.06755*([tempSquared]))-(0.002237*([tempCubed]))+(0.0001321*([tempToTheFourth]))";

dt.Columns.Add("calc33", typeof(float));
dt.Columns["calc33"].Expression = "(33+([duration]*6))-(0.3779*[temp])+(0.04758*([tempSquared]))-(0.0017*([tempCubed]))+(0.000151*([tempToTheFourth]))";

Smoothed Primary Calculations

Now you can add another two calculated columns to hold the 'smoothed' version of each of your two primary calculations.

dt.Columns.Add("calc23Smooth", typeof(float));
dt.Columns["calc23Smooth"].Expression = "IIF([calc23] < 70, 70, IIF( [calc23] > 200, 200, [calc23]))";

dt.Columns.Add("calc33Smooth", typeof(float));
dt.Columns["calc33Smooth"].Expression = "IIF([calc33] < 70, 70, IIF( [calc33] > 200, 200, [calc33]))";

Final Calculation

The other portions of your posted expression are also suffering from a lot of repetition and I won't say that I fully grok exactly what you're trying to do. The depth and parentheses arrangement are a little bit too much for me. But here's something close to what I think you're going for.

Say you were trying to calculate using a number of different 'rules' conditional on the ranges of the datapoint [temp], and each range had either a calculation or a hardcoded value, such as:

  [temp value]   [returns value]
  <0           = 0 
  >=0 and <20  = [calc23Smooth]
  >=20 and <25 = 60
  >=25 and <30 = 50

  >=30 and <35 = [calc33Smooth]
  >=35 and <40 = 40
  >=40 and <45 = 30

You could leverage the existing calculated columns and get the final calculation as follows:

dt.Columns.Add("calcFinal", typeof(float));
dt.Columns["calcFinal"].Expression =
    "IIF([temp] < 0, 0, IIF([temp] < 20, [calc23Smooth], IIF([temp] < 25, 60, IIF([temp] < 30, 50, IIF([temp] < 35, [calc33Smooth], IIF( [temp] < 40, 40, IIF( [temp] < 45, 50, 0)))))))";

While I may not have the final calculation the way you want it, I think the number of buckets/rules is the same and we are now using only 7 levels of IIF() depth. We reduced that by close-to-half and the total length of our final calculation expression is getting down close to reasonable.

I'm confident that you can work out the details from there.