Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

DataTable calculated field question

I have a datatable that has a grouping in it and I want to be able to assign a value to a calculated field based on the first (or matching) fields in the group.

For example, if I have the following table:

MyGroup       MyDateVar
gr-1                2001-12-23
gr-1                2004-03-12
gr-1                2014-10-04
gr-1                2003-12-23
gr-1                2012-03-12
gr-1                2000-10-04

I would like to have a calculated column that would have a value (say 10) for the oldest MyDateVar for the group.  The final result would be:


MyGroup       MyDateVar      CalcField
gr-1                2001-12-23      0
gr-1                2004-03-12      0
gr-1                2014-10-04      10
gr-1                2003-12-23      0
gr-1                2012-03-12      10
gr-1                2000-10-04      0

Can this be done with a datacolumn calculated field?
0
hotshottools
Asked:
hotshottools
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
No

Surely possible by either the underlying query (server side) or using some LINQ (client side) ..
0
 
louisfrCommented:
To do that, you need an auxilliary table and a relation between your table (child) and the auxilliary (parent).

Parent table contains columns MyGroup, OldestDate
Child table contains columns MyGroup, MyDateVar, CalcField
Set OldestDate's Expression property to "Min(Child.MyDateVar)"
Set CalcField's Expression property to "iif(MyDateVar=Parent.OldestDate, 10, 0)"

Only constraint: you have to add a row in the parent table for each group. Nothing impossible to make automatic.

Here's a bit of code I typed to test all this.
class CT : DataTable
{
    public event DataTableNewRowEventHandler RowAdded;
    public void AddRow(params object[] data)
    {
        var row = NewRow();
        row.ItemArray = data;
        var ev = RowAdded;
        if (ev != null)
        {
            ev(this, new DataTableNewRowEventArgs(row));
        }
        Rows.Add(row);
    }
}

static void Main()
{
    DataTable parent = new DataTable
    {
        Columns = {
            "MyGroup",
            { "OldestDate", typeof(DateTime) }
        }
    };
    CT child = new CT
    {
        Columns = {
            "MyGroup",
            { "MyDateVar", typeof(DateTime) },
            { "CalcField", typeof(int) }
        }
    };
    parent.PrimaryKey = new[] { parent.Columns["MyGroup"] };
    DataSet ds = new DataSet
    {
        Tables = { parent, child },
        Relations = { new DataRelation("r", parent.Columns["MyGroup"], child.Columns["MyGroup"]) }
    };
    parent.Columns["OldestDate"].Expression = "Min(Child.MyDateVar)";
    child.Columns["CalcField"].Expression = "iif(MyDateVar=Parent.OldestDate,10,0)";
    child.RowAdded += (o, e) => {
        if (!parent.Rows.Contains(e.Row["MyGroup"]))
            parent.Rows.Add(e.Row["MyGroup"]);
    };
    child.AddRow("gr1", new DateTime(2014, 10, 1));
    child.AddRow("gr1", new DateTime(2014, 10, 2));
    child.AddRow("gr1", new DateTime(2014, 10, 1));
    child.AddRow("gr1", new DateTime(2014, 1, 1));
}

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now