Compare data in different rows but same column

5 posts, 2 answers
  1. Matt
    Matt avatar
    91 posts
    Member since:
    Jun 2012

    Posted 11 Oct 2018 Link to this post

         Can someone provide a working example of how we can compare data in different rows but within the same column? Or see what I'm missing?

    Below is a function that throws an error due to an index not existing... but the data populates. If I remove most of the code and add an alert box, it shows the itemindex #.

    This grid is populated from code-behind and the columns are dynamic (meaning, specified by the data, not in the markup).

    ADMIN EDIT: Solution created by OP: https://www.telerik.com/forums/compare-data-in-different-rows-but-same-column#4BzUDXkNFU2U_FM8atHFbw.

    protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
            {
                RadGrid radGrid = (RadGrid)sender;
                if (e.Item.ItemType == GridItemType.Item || e.Item.ItemType == GridItemType.AlternatingItem)
                {
                    GridDataItem gridDataItem = (GridDataItem)e.Item;
                    if (e.Item.ItemIndex > 0)
                    {
                        decimal fte;
                        decimal fteTarget;
                        GridDataItem previousGridDataItem = radGrid.Items[1];
                        GridDataItem currentGridDataItem = radGrid.Items[2];
                        for (int c = 1; c < previousGridDataItem.Cells.Count; c++)
                        {
                            if (decimal.TryParse(previousGridDataItem.Cells[c].Text, out fte) && decimal.TryParse(currentGridDataItem.Cells[c].Text, out fteTarget))
                            {
                                if (fte <= fteTarget)
                                {
                                    previousGridDataItem.Cells[c].ForeColor = Utility.Green;
                                }
                                else if (fte > fteTarget)
                                {
                                    previousGridDataItem.Cells[c].ForeColor = Utility.Red;
                                }
                            }
                        }
                    }
    }

  2. Matt
    Matt avatar
    91 posts
    Member since:
    Jun 2012

    Posted 11 Oct 2018 in reply to Matt Link to this post

    I forgot to add that the grid I'm trying to use this on is nested in a parent grid.
  3. Answer
    Marin Bratanov
    Admin
    Marin Bratanov avatar
    4494 posts

    Posted 11 Oct 2018 Link to this post

    Hi Matt,

    Since that's related to the data, I'd consider putting the necessary information in the data source itself, then it would be easy to access it per-row without needing to go through the rest of the rows.

    Also, to have all rows available to compare, I'd suggest changing their CssClass in the PreRender event, as ItemDataBound occurs as data is put in the rows, so the next rows will not have their data at the time of the event.

    Here's an example I created for you:

    protected void rg1_PreRender(object sender, EventArgs e)
    {
        for (int i = 1; i < rg1.MasterTableView.Items.Count; i++)//start from 1, we don't have what to compare the first row against. This also helps avoid index out of bounds exceptions
        {
            GridDataItem currItem = rg1.MasterTableView.Items[i];
            GridDataItem prevItem = rg1.MasterTableView.Items[i - 1];
     
            foreach (GridColumn col in rg1.MasterTableView.RenderColumns)//if we don't know the columns we want to check, we have to loop them all
            {
                if (col is GridBoundColumn && col.DataType.Equals(typeof(decimal)) || col.DataType.Equals(typeof(int))) {//we can compare only numbers, of course this list isn't exhaustive. You can devise your own logic here that will determine which columns to check. This code checks bound columns only to avoid attempting to compare the structural columns of the grid so you can use this as base
     
                    GridBoundColumn theCol = col as GridBoundColumn;//cast so we can use the data field names
     
                    if (decimal.Parse((prevItem.DataItem as DataRowView)[theCol.DataField].ToString()) < decimal.Parse((currItem.DataItem as DataRowView)[theCol.DataField].ToString())) {//implement desired comparison. This one is a little crude and could do with some refactoring. Of course, the data item cast depends on your data source, this one is a DataTable
                        currItem[col.UniqueName].CssClass = "specialClass";//if condition met - set class to the cell
                    }
            }
            }
        }
    }
     
    protected DataTable GetDummyData()
    {
        DataTable tbl = new DataTable();
        tbl.Columns.Add(new DataColumn("firstField", typeof(decimal)));
        tbl.Columns.Add(new DataColumn("textField", typeof(string)));
        tbl.Columns.Add(new DataColumn("valueField", typeof(int)));
        tbl.Columns.Add(new DataColumn("fourthField", typeof(string)));
        tbl.Rows.Add(new object[] { 1, "one", 1, "red" });
        tbl.Rows.Add(new object[] { 1, "two", 2, "green" });
        tbl.Rows.Add(new object[] { 5, "three", 3, "blue" });
        tbl.Rows.Add(new object[] { 6, "four", 1, "pink" });
     
        return tbl;
    }
     
    protected void rg1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        (sender as RadGrid).DataSource = GetDummyData();
    }

    Where the markup only needs to attach the event handlrs so columns get auto generated from the data source fields:

    <telerik:RadGrid runat="server" ID="rg1" OnNeedDataSource="rg1_NeedDataSource" OnPreRender="rg1_PreRender"></telerik:RadGrid>

    and here is a really simple css class just to showcase this works (I do not generally condone the !important modifier, but it helps for quick test to confirm something works):

    <style>
        .specialClass {
            background: red !important;
        }
    </style>

     

    And the expected result is attached below.


    Regards,
    Marin Bratanov
    Progress Telerik
    Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
  4. Answer
    Matt
    Matt avatar
    91 posts
    Member since:
    Jun 2012

    Posted 11 Oct 2018 in reply to Marin Bratanov Link to this post

    Thanks. I think you're response will help developers that come across this but I figured it out... it works for my situation but perhaps others might need it.

    Here's the mark up:

    <telerik:RadGrid ID="RG_Closest" runat="server" EnableLinqExpressions="false" AutoGenerateColumns="False" CellSpacing="-1" GridLines="None" AllowCustomPaging="false" AllowMultiRowSelection="False" AllowPaging="false" AllowSorting="false" ShowFooter="false" ShowGroupPanel="false" OnItemDataBound="RG_Closest_ItemDataBound" style="margin:0px 5px 10px 5px" CssClass="hvr-glow" Width="100%" >
                        <ClientSettings AllowColumnsReorder="False" AllowDragToGroup="False" ReorderColumnsOnClient="True" EnableRowHoverStyle="true">
                            <Selecting AllowRowSelect="True" />
                            <Scrolling AllowScroll="False" UseStaticHeaders="True" />
                        </ClientSettings>
                        <MasterTableView CommandItemDisplay="None" ShowFooter="false" ShowHeadersWhenNoRecords="true" GroupLoadMode="Server" RetainExpandStateOnRebind="true" DataKeyNames="CC">
                            <CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" />
                            <HeaderStyle HorizontalAlign="Center" />
                            <AlternatingItemStyle HorizontalAlign="Center" />
                            <ItemStyle HorizontalAlign="Center" />
                            <NestedViewSettings>
                                <ParentTableRelation>
                                    <telerik:GridRelationFields MasterKeyField="CC" DetailKeyField="CC" />
                                </ParentTableRelation>
                            </NestedViewSettings>
                            <NestedViewTemplate>
                            <telerik:RadGrid ID="RG_ClosestFTE" runat="server" AutoGenerateColumns="False" CellSpacing="-1" GridLines="None" AllowCustomPaging="false" AllowMultiRowSelection="False" AllowPaging="false" AllowSorting="false" ShowFooter="false" ShowGroupPanel="false" style="margin:0px 5px 10px 5px" Width="98%">
                                <ClientSettings AllowColumnsReorder="False" AllowDragToGroup="False" ReorderColumnsOnClient="True" EnableRowHoverStyle="true">
                                    <Selecting AllowRowSelect="True" />
                                    <Scrolling AllowScroll="False" UseStaticHeaders="True" />
                                </ClientSettings>
                                    <MasterTableView CommandItemDisplay="None" ShowFooter="false" ShowHeadersWhenNoRecords="true">
                                        <HeaderStyle HorizontalAlign="Center" />
                                        <AlternatingItemStyle HorizontalAlign="Center" />
                                        <ItemStyle HorizontalAlign="Center" />
                                        <NoRecordsTemplate>
                                            <div>No records to display</div>
                                        </NoRecordsTemplate>
                                    </MasterTableView>
                                </telerik:RadGrid>
                            </NestedViewTemplate>
                            <Columns>
                                <telerik:GridBoundColumn DataField="Dot10Desc" HeaderText="Financial Center" SortExpression="Dot10Desc" UniqueName="Dot10Desc" ItemStyle-HorizontalAlign="Left"/>
                                <telerik:GridBoundColumn DataField="CC" HeaderText="Co/CC" SortExpression="CC" UniqueName="CC"/>
                                <telerik:GridBoundColumn DataField="Participating" HeaderText="Participating" SortExpression="Participating" UniqueName="Participating"/>
                                <telerik:GridNumericColumn DataField="Distance" HeaderText="Distance (mi)" SortExpression="Distance" UniqueName="Distance" />
                            </Columns>
                            <NoRecordsTemplate>
                                <div style="padding-left:10px">No requests to display</div>
                            </NoRecordsTemplate>
                        </MasterTableView>
                    </telerik:RadGrid>

     

     

    I call this after the databind event.

    private void ClosesFTE_ColorMarker()
            {
                decimal fte;
                decimal fteTarget;
     
                foreach (GridDataItem gridDataItem in RG_Closest.MasterTableView.Items)
                {
                    GridNestedViewItem gridNestedViewItem = (GridNestedViewItem)gridDataItem.ChildItem;
                    RadGrid RG_ClosestFTE = (RadGrid)gridNestedViewItem.FindControl("RG_ClosestFTE");
     
                    GridDataItem previousGridDataItem = RG_ClosestFTE.Items[0];
                    GridDataItem currentGridDataItem = RG_ClosestFTE.Items[1];
     
                    for (int c = 1; c < previousGridDataItem.Cells.Count; c++)
                    {
                        if (decimal.TryParse(previousGridDataItem.Cells[c].Text, out fte) && decimal.TryParse(currentGridDataItem.Cells[c].Text, out fteTarget))
                        {
                            if (fteTarget != 0)
                            {
                                if (fte <= fteTarget)
                                {
                                    previousGridDataItem.Cells[c].ForeColor = Utility.Green;
                                }
                                else if (fte > fteTarget)
                                {
                                    previousGridDataItem.Cells[c].ForeColor = Utility.Red;
                                }
                            }
                            else
                            {
                                previousGridDataItem.Cells[c].ForeColor = Utility.Red;
                            }
                        }
                    }
                }
            }
  5. Marin Bratanov
    Admin
    Marin Bratanov avatar
    4494 posts

    Posted 11 Oct 2018 Link to this post

    It's good to hear you have resolved this, Matt. I took the liberty of linking your solution from your first post for anyone else who may find this thread.
    --Marin
Back to Top