Excel/Expression/Math operation on DataTable

Question

INPUT
DataTable1 --- table1
|Parameter Name| Lower |Upper |P90 point |P10 point |P50 point |
Diff_IC[1] | 1.5 |2.5 |0.147183999 |0.137499005 |0.141139999 |
Prog_IC[2] |-10 |1000 |0.148519993 |0.137592003 |0.141236007 |
Nam_IC[3] |-64 |64 |0.157493005 |0.117183459 |0.141788006|
ADCI_N[1 ] |-0.8 |-0.1 |0.138519993 |0.117592003| 0.142398 |

Output
|Parameter Name| Lower |Upper |P90 point |P10 point | P50 point |Sigma |CpK |
Diff_IC[1] | 1.5 | 2.5 |0.147183999 |0.137499005 |0.141139999| | |
Prog_IC[2] |-10 |1000 |0.148519993 |0.137592003 |0.141236007| | |
Nam_IC[3] |-64 |64 |0.117183459 |0.157493005 |0.141788006| | |
ADCI_N[1 ] |-0.8 | -0.1 |0.138519993|0.117592003 |0.142398 | | |

Formula: Sigma = P90-P10/2.56 Cpk = Minimum|[ (P50 point - Lower) / 3 (sigma), (upper - P50 pint) / 3 (sigma)]|

Vulpes · Answer

Well, it looks like all the existing columns are strings as no data type is specified when creating the columns. So I'd try this code: DataColumn sigma = table1.Columns.Add("Sigma", typeof(double)); DataColumn cpK = table1.Columns.Add("CpK", typeof(double)); // set their expression properties sigma.Expression = "Convert(point90,'System.Double') - Convert(point10,'System.Double')/2.56"; cpK.Expression = "IIF((Convert(point50,'System.Double') - Convert(LSL,'System.Double'))

Vulpes · Answer

As these formulas are not too complicated, you should be able to compute the Sigma and CpK properties using the DataColumn.Expression proiperty: // add new columms to table1 DataColumn sigma = table1.Columns.Add("Sigma", typeof(double)); DataColumn cpK = table1.Columns.Add("CpK", typeof(double)); // set their expression properties sigma.Expression = "P90 - P10/2.56"; cpK.Expression = "IIF(([P50 point] - Lower)

Farhan Shariff · Answer

Thank You Vulpes :). It work like a charm

Vulpes · Answer

The Expression property only allows you to use a few functions and, unfortunately, Math.Min isn't one of them. You have to use IIF instead. So, here, I'd use: cpK.Expression = "IIF(cpKL As cpKL and cpKR are already doubles, you don't need to convert them.

Farhan Shariff · Answer

want to put Minimum Value of (cpKL,cpKR) into cpK How to write the expression for cpK sigma.Expression = "(Convert(point90,'System.Double')-Convert(point10,'System.Double'))/2.56"; cpKL.Expression = "(Convert(point50,'System.Double')- Convert(LSL,'System.Double'))/(3.0*sigma)"; cpKR.Expression = "(Convert(USL,'System.Double') - Convert(point50,'System.Double'))/(3.0*sigma)"; // The expression contains undefined function call Math.Min(). //cpK.Expression = "Math.Min((Convert(cpKL,'System.Double'),(Convert(cpKR,'System.Double'))";

Farhan Shariff · Answer

Consider only this .Here I have to inser new column Sigma and cpk and find the values using System; using System.Data; using Microsoft.VisualBasic.FileIO; namespace ReadDataFromCSVFile { static class Program { static void Main() { string csv_file_path = @"C:\Matlab\limit_sheet.csv"; DataTable table1 = GetDataTabletFromCSVFile(csv_file_path); } private static DataTable GetDataTabletFromCSVFile(string csv_file_path) { DataTable table1 = new DataTable(); using (TextFieldParser csvReader = new TextFieldParser(csv_file_path)) { csvReader.SetDelimiters(new string[] { "," }); csvReader.HasFieldsEnclosedInQuotes = true; string[] colFields = csvReader.ReadFields(); foreach (string column in colFields) { DataColumn datecolumn = new DataColumn(column); datecolumn.AllowDBNull = true; table1.Columns.Add(datecolumn); } while (!csvReader.EndOfData) { string[] fieldData = csvReader.ReadFields(); //Making empty value as null for (int i = 0; i { if (fieldData[i] == "") { fieldData[i] = null; } } table1.Rows.Add(fieldData); } } /* DataColumn sigma = table1.Columns.Add("Sigma", typeof(double)); DataColumn cpK = table1.Columns.Add("CpK", typeof(double)); // set their expression properties sigma.Expression = "point90 - point10 /2.56"; cpK.Expression = "IIF(([point50] - LSL) return table1; } } }

Farhan Shariff · Answer

I don't understand.

Vulpes · Answer

I was expecting all the columns except the first to be doubles. But, if COL is a string, then replace it in the Expression property with: Convert(COL, 'System.Double')

Farhan Shariff · Answer

cpK.Expression = "IIF(([point50] - LSL) Cannot perform '-' operation on System.Double and System.String. sigma.Expression = "point90 - point10 /2.56"; Error cannot perform / on System.String

Excel/Expression/Math operation on DataTable

Answers (9)