locked
Summarizing data table rows based on specific column using C# RRS feed

  • Question

  • Hi Everyone,
    I need a logic to implement for the below.
    I have a C# console application. In that, I am generating Journal, which is in the form of data table. I am finally writing into a file in a csv format. I am able to do that.
    In data table, I have numbers of rows as shown below. I need to summarize the rows based on Debit and Credit column for other column same data. Suppose I have 100 rows in the data table in which text1 is 20 times, 10 times with Debit columns values and 10 times with credit columns values. Therefore, for those 20 rows, I need to summarize 1 rows for credit 1 rows for debit.  Value in credit and debit will be added respectively.
    Let us take an example as below

    Year

    Entity

    Account

    CostCenter

    Currency

    BusUnit

    Debit

    Credit

    Text

    Date

    2020

    40001

    80111

    AED

    101

    100

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    17891

    AED

    101

    100

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    80111

    AED

    101

    200

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    17891

    AED

    101

    200

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    80111

    AED

    101

    300

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    17891

    AED

    101

    300

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    12122

    AED

    201

    250

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    22222

    AED

    201

    250

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    12122

    AED

    201

    350

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    22222

    AED

    201

    350

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    323232

    AED

    301

    500

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    131313

    AED

    301

    500

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    323232

    AED

    301

    100

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    131313

    AED

    301

    100

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    323232

    AED

    301

    200

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    131313

    AED

    301

    200

    Salaries Feb 29 - abcd project

    29/02/2020


    output should be as below.

    Mohammad Nadeem Alam SME in Emirates NBD


    Sunday, May 24, 2020 6:47 PM

Answers

  • Hi,

    “dataTable1.DefaultView.Sort” will not actually modify the order of the rows in the DataTable, but will change the order in which it is displayed.

    I mean, if you display it in the DataGridView, you will find that it has been sorted.

    If you just want to see the sorted results in the program, this is good. If you have special requirements, such as importing it into a file, this will not work. In this case, the following code is required.

                DataTable dataTable = GetData();
    
                DataTable dataTable1 = GetResDataTable(dataTable, "Debit");
                DataTable dataTable2 = GetResDataTable(dataTable, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                DataRow[] foundRows = dataTable1.Select("", "Text");
                DataTable dt = foundRows.CopyToDataTable();
                dataGridView1.DataSource = dt;

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 26, 2020 6:55 AM
  • Hi Mohammad,

    You should be able to use something like this:

    .GroupBy(r => new { Text = r["Text"], ICP = r["ICP"], Account = r["Account"] })
    .Select(g=>
    {
        DataRow row = dataTable.NewRow();
        row["Year"] = g.First()["Year"];
        row["Entity"] = g.First()["Entity"];
        row["Account"] = g.Key.Account;
        row["Project"] = g.First()["Project"];
        row["Location"] = g.First()["Location"];
        row["CostCenter"] = g.First()["CostCenter"];
        row["ICP"] = g.Key.ICP;
        row["Employee"] = g.First()["Employee"];
        // etc. etc.
        row["Text"] = g.Key.Text;
        // etc.etc.
    })
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, June 9, 2020 3:07 PM
  • Sorry about that Mohammad, I wasn't thinking (needed more coffee)! This should be better, go back to the original code from Timon and add an if after it:

    row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
    if (row["Debit"] == 0)
        row["Debit"] = DBNull.Value;
    row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));
    if (row["Credit"] == 0)
        row["Credit"] = DBNull.Value;

    Sometimes trying to be clever doesn't end up being so clever!!  ;0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 26, 2020 5:18 PM
  • Hi Timon,

    Yes it is resolved. Thanks a lot for your support. Yes I will mark it as answer.


    Mohammad Nadeem Alam SME in Emirates NBD

    Wednesday, May 27, 2020 8:29 AM

All replies

  • I need output as below.

    Year

    Entity

    Account

    CostCenter

    Currency

    BusUnit

    Debit

    Credit

    Text

    Date

    2020

    40001

    80111

    AED

    101

    600

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    17891

    AED

    101

    600

    Salaries Feb 29 - HO Overheads

    29/02/2020

    2020

    40001

    12122

    AED

    201

    600

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    22222

    AED

    201

    600

    Salaries - Feb 2020

    29/02/2020

    2020

    40001

    323232

    AED

    301

    800

    Salaries Feb 29 - abcd project

    29/02/2020

    2020

    40001

    131313

    AED

    301

    800

    Salaries Feb 29 - abcd project

    29/02/2020

    Please note that I need some efficient way to do that. In actual I have large number of rows in the data table (millions rows). Using LINQ should be also fine. I need efficient method. Please help me.


    Mohammad Nadeem Alam SME in Emirates NBD

    Sunday, May 24, 2020 6:48 PM
  • Hi,

    Thank you for posting here.

    You can use GroupBy in linq to group them, see the following code.

                DataTable dataTable = GetData();
           
                var dt1 = dataTable.AsEnumerable()
                  .GroupBy(r => r["Account"])
                  .Select(g =>
                  {
                      DataRow row = dataTable.NewRow();
                      row["Year"] = g.First()["Year"];
                      row["Entity"] = g.First()["Entity"];
                      
                      row["Account"] = g.Key;
                      
                      row["CostCenter"] = g.First()["CostCenter"];
                      row["Currency"] = g.First()["Currency"];
                      row["BusUnit"] = g.First()["BusUnit"];
    
                      row["Debit"] = g.Sum(r => r.Field<int>("Debit"));
                      row["Credit"] = g.Sum(r => r.Field<int>("Credit"));
    
                      row["Text"] = g.First()["Text"];
                      row["Date"] = g.First()["Date"];
                      return row;
                  }).CopyToDataTable();

    It should be noted that if Debit and Credit are money, please use Decimal Struct.

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 25, 2020 7:50 AM
  • You can check here.
    Monday, May 25, 2020 3:01 PM
  • You can check here.
    You did not provide a link ...

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, May 25, 2020 3:47 PM
  • Hi Timon,

    Thanks a lot for your reply. Actually this is what I was looking for. But now when I am running the above code getting below error

    Unable to cast object of type 'System.String' to type 'System.Int32'.

    It may be simple, but I am not able to resolve it. can you please advice how to fix this cast error.

    Please note that in the data table all the columns defined as string. while calculation for sum, it require to convert into decimal or double.


    Mohammad Nadeem Alam SME in Emirates NBD

    Monday, May 25, 2020 4:41 PM
  • Hi Mohammed,

    You'll also have to test for null values. This might take care of the Debit/Credit summation (untested):

    row["Debit"]  = g.Sum(r => Field("Debit") == DBNull.Value ? 0 : r.Field<decimal>("Debit"));
    row["Credit"] = g.Sum(r => Field("Credit") == DBNull.Value ? 0 : r.Field<decimal>("Credit"));
    If that doesn't work, then this should (also untested):

    row["Debit"]  = g.Sum(r => Field("Debit")  == DBNull.Value ? 0 : decimal.Parse(r.Field("Debit")));
    row["Credit"] = g.Sum(r => Field("Credit") == DBNull.Value ? 0 : decimal.Parse(r.Field("Credit")));



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Proposed as answer by Naomi N Tuesday, May 26, 2020 3:32 AM
    Monday, May 25, 2020 5:12 PM
  • Hi Hi Timon,

    The first Field, is not recognizing. it is giving below error.

    Before calculation, shall I assign zero to fields where ever it empty. 


    Mohammad Nadeem Alam SME in Emirates NBD

    Monday, May 25, 2020 5:30 PM
  • Hi Hi Timon,

    I managed to run above code. I assigned all the empty column to 0.00 and I have changed data type of the column Debit and credit to decimal. after that code is not giving any error. But as an output I am getting the same table. I can not see any summarized rows group by account for debit and credit.


    Mohammad Nadeem Alam SME in Emirates NBD

    Monday, May 25, 2020 8:27 PM
  • Hi Hi Timon,

    I am sorry, I am getting the result. But the result is not as per expected. I need a result like for distinct text there should be two rows one for debit and one for credit. account value may be same. for example we have 20 rows in which two distinct  text values  are Tex1 (10 times) and Tex2 (10 times). In which for Text1 5 records will be for credit and 5 records will be for Debit same way for Text2.

    so as an output there will be 4 rows with two distinct text. for a particular text there will be two row one for Debit and one for credit. account values may not necessarily will be different . it may same. In provided code I am getting record base on distinct account. Actually in example I have provided different accounts for distinct text where as in actual account may be same for different text.

    summation of Debit or Credit will be based on text. Debit and Credit value will be always same. but corresponding column value will be different. In above code if I change the group by Text then I get rows as half. I mean in place of 8 rows getting for rows as for rows is only distinct.

    can you please provide updated query with condition so that it gives desired output. I think it may require where condition.


    Mohammad Nadeem Alam SME in Emirates NBD


    Monday, May 25, 2020 8:57 PM
  • You haven't shown your current code, Mohammad, so it's hard for me to give you an example based on your code. 

    But, I think that if you do two queries with a WHERE clause (one where the Debit column is not DBNull.Value and one where the Credit column is not DBNull.Value) and then use a Union to "merge" the two results together, you might get what you're looking for. If you can't figure it out, post your code and I can probably show you what needs changing.

    Basically though, you want something like (again, totally untested code, but hopefully the syntax is right):

    var dt1 = dataTable.AsEnumerable()
        .Where(R => R.Field("Debit") != DBNull.Value)
        ...
        //other stuff, like the GroupBy
        ...
        .Select(G =>
        {
                ...
                row["Debit"] = G.Sum(R.Field<decimal>("Debit"));
                row["Credit"] = 0;
                ...
        }
    .Union(dataTable.AsEnumerable()
        .Where(R => R.Field("Credit") != DBNull.Value)
        ...
        //other stuff, like the GroupBy
        ...
        .Select(G =>
        {
                ...
                row["Debit"] = 0; 
                row["Credit"] = G.Sum(R.Field<decimal>("Credit"));
                ...
        })
    )



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Monday, May 25, 2020 11:31 PM clarified the DBNull.Value stuff
    Monday, May 25, 2020 11:08 PM
  • Hi,

    I wrote some code based on Bonnie's idea, please see if it makes what you want.

            private void Form1_Load(object sender, EventArgs e)
            {
                DataTable dataTable = GetData();
                DataTable dataTable1 = GetResDataTable(dataTable, "Debit");
                DataTable dataTable2 = GetResDataTable(dataTable, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                dataTable1.DefaultView.Sort = "BusUnit";
                dataGridView1.DataSource = dataTable1;
            }
            private DataTable GetResDataTable(DataTable dataTable, string str) 
            {
                var dt1 = dataTable.AsEnumerable()
                   .Where(r => r.Field<string>(str) != String.Empty)
                 .GroupBy(r => r["Text"])
                 .Select(g =>
                 {
                     DataRow row = dataTable.NewRow();
                     row["Year"] = g.First()["Year"];
                     row["Entity"] = g.First()["Entity"];
    
                     row["CostCenter"] = g.First()["CostCenter"];
                     row["Currency"] = g.First()["Currency"];
                     row["BusUnit"] = g.First()["BusUnit"];
    
                     row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
                     row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));
    
                     row["Text"] = g.First()["Text"];
                     row["Date"] = g.First()["Date"];
                     return row;
                 }).CopyToDataTable();
                return dt1;
            }

    Result:

    I did not put these data into the database but created a new datatable, so in the code, my judgment condition is "! = string.Empty", in the actual code, we should use DBNull.Value.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 26, 2020 3:26 AM
  • What is your back-end language? I think the simplest solution for your problem would be to simply run appropriate query in that language (say, in T-SQL for SQL Server) rather than trying LINQ query.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 26, 2020 3:35 AM
  • Hi Timon,

    This is perfectly fine. I am getting the output. only one small issue. I am getting the out put first all Debit and then all credit rows. In actual looking for a particular text, first Debit row and for same text credit row. If we can manage to do that will be great. In your output it is showing fine, where as in my output it is as below. do it require again sorting?.


    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, May 26, 2020 5:44 AM
  • Hi,

    In my code, there is a "BusUnit" column in my result datatable, so I used

    dataTable1.DefaultView.Sort =" BusUnit ";

    to sort it, you can add this column in linq or use "Text" to sort it.

     dataTable1.DefaultView.Sort = "Text";

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 26, 2020 5:54 AM
  • Hi Timon,

    Thanks for your response so quickly. That I have tried, sorting by Text. But it is giving the same output.

     DataTable dataTable1 = GetResDataTable(dtIntercompanyJournalSchema, "Debit");
                DataTable dataTable2 = GetResDataTable(dtIntercompanyJournalSchema, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                dataTable1.DefaultView.Sort = "Text";


    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, May 26, 2020 6:18 AM
  • Hi,

    “dataTable1.DefaultView.Sort” will not actually modify the order of the rows in the DataTable, but will change the order in which it is displayed.

    I mean, if you display it in the DataGridView, you will find that it has been sorted.

    If you just want to see the sorted results in the program, this is good. If you have special requirements, such as importing it into a file, this will not work. In this case, the following code is required.

                DataTable dataTable = GetData();
    
                DataTable dataTable1 = GetResDataTable(dataTable, "Debit");
                DataTable dataTable2 = GetResDataTable(dataTable, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                DataRow[] foundRows = dataTable1.Select("", "Text");
                DataTable dt = foundRows.CopyToDataTable();
                dataGridView1.DataSource = dt;

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 26, 2020 6:55 AM
  • Hi Timon,

    I have C# console application. Yes I have requirement to put the result into a file as .csv.

    I have tried above code. It seems it is working fine. Thanks a lot for your support.

    Just one thing , we are putting 0 in Debit and credit as we are summing the column value, in case we don't assign 0 , it will give error. once the result is ready I want to put it back as empty not as 0. Can you please tell me do the easy and efficient  way to do the same. I can do it by looping. But I don't think that will be efficient way. Preferably using LINQ only.


    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, May 26, 2020 8:38 AM

  • If you're using Timon's code, change this:

    row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
    row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));


    to this:

    row["Debit"] =  r.Field("Debit") == DBNull.Value ? r.Field("Debit") : g.Sum(r => Decimal.Parse(r.Field<string>("Debit")));
    row["Credit"] =  r.Field("Credit") == DBNull.Value ? r.Field("Credit") : g.Sum(r => Decimal.Parse(r.Field<string>("Credit")));


    This might work too:

    row["Debit"] =  r.Field("Debit") == DBNull.Value ? r.Field("Debit") : g.Sum(r => r.Field<decimal>("Debit"));
    row["Credit"] =  r.Field("Credit") == DBNull.Value ? r.Field("Credit") : g.Sum(r => r.Field<decimal>("Credit"));

    EDIT: Now that I think about it further, it might be better to do the null value like this:

    ..... r.Field("Debit") == DBNull.Value ? DBNull.Value .....



    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Tuesday, May 26, 2020 3:31 PM
    Tuesday, May 26, 2020 3:24 PM
  • Hi Bonnie,

    Please find the below code, provided by Timon,

    here just to show you I am loading it from a .csv file

    you can think like on main method below code 

     string path = @"C:\WDIFS\AU\OUT\Journal.csv";
                DataTable Journal = new DataTable();           
                Journal = ConvertCSVtoDataTable(path);            
                DataTable dataTable1 = GetResDataTable(Journal, "Debit");
                DataTable dataTable2 = GetResDataTable(Journal, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                DataRow[] foundRows = dataTable1.Select("", "Text");
                DataTable dt = foundRows.CopyToDataTable();

    and calling the method for grouping summarizing is as below

    public static DataTable GetResDataTable(DataTable dataTable, string str)
            {
                var dt1 = dataTable.AsEnumerable()
                   .Where(r => r.Field<string>(str) != String.Empty)
                 .GroupBy(r => r["Text"])
                 .Select(g =>
                 {
                     DataRow row = dataTable.NewRow();
                     row["Year"] = g.First()["Year"];
                     row["Entity"] = g.First()["Entity"];
                     //row["Account"] = G.Key;
                     row["Account"] = g.First()["Account"];
                     row["Project"] = g.First()["Project"];
                     row["Location"] = g.First()["Location"];
                     row["CostCenter"] = g.First()["CostCenter"];
                     row["ICP"] = g.First()["ICP"];
                     row["Employee"] = g.First()["Employee"];
                     row["Asset"] = g.First()["Asset"];
                     row["Currency"] = g.First()["Currency"];
                     row["BusUnit"] = g.First()["BusUnit"]; ;

                     // row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
                     //row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));                

                    row["Debit"] = r.Field("Debit") == DBNull.Value ? r.Field("Debit") : g.Sum(r => Decimal.Parse(r.Field<string>("Debit")));
                    row["Credit"] = r.Field("Credit") == DBNull.Value ? r.Field("Credit") : g.Sum(r => Decimal.Parse(r.Field<string>("Credit")));

                     //row["Debit"] = r.Field("Debit") == DBNull.Value ? r.Field("Debit") : g.Sum(r => r.Field<decimal>("Debit"));
                     //row["Credit"] = r.Field("Credit") == DBNull.Value ? r.Field("Credit") : g.Sum(r => r.Field<decimal>("Credit"));

                     row["Text"] = g.Key;
                     row["Date"] = g.First()["Date"];
                     row["VoucherType"] = g.First()["VoucherType"];
                     row["SubProjectID"] = g.First()["SubProjectID"];
                     row["Activity"] =g.First()["Activity"];
                     return row;                
                 }).CopyToDataTable();
                return dt1;
            }

    when applying your suggested change, giving below error. The name r does not exist in the current context. One more thing,please note there is no DBNull value, it is always empty. data is not coming from DB. it is coming from .CSV.


    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, May 26, 2020 4:38 PM
  • Sorry about that Mohammad, I wasn't thinking (needed more coffee)! This should be better, go back to the original code from Timon and add an if after it:

    row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
    if (row["Debit"] == 0)
        row["Debit"] = DBNull.Value;
    row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));
    if (row["Credit"] == 0)
        row["Credit"] = DBNull.Value;

    Sometimes trying to be clever doesn't end up being so clever!!  ;0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 26, 2020 5:18 PM
  • Hi Bonnie,

    Thanks No problem.

    As you suggested , I applied the if condition as below

    if (row["Debit"].ToString() == "0")
                         row["Debit"] = DBNull.Value;

    if (row["Credit"].ToString() == "0")
                         row["Credit"] = DBNull.Value;

    applying below giving  operator error as data type is different

    if (row["Debit"].ToString() == "0")
                         row["Debit"] = DBNull.Value;


    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, May 26, 2020 6:31 PM
  • Darn! That's what I meant to say!  (needing the .ToString) ... sorry Mohammad!!  

    But at least row[Debit"] = DBNull.Value; works, doesn't it?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 26, 2020 7:40 PM
  • Hi Bonnie,

    row[Debit"] = DBNull.Value yes it works fine. Thank you so much.



    Mohammad Nadeem Alam SME in Emirates NBD

    Wednesday, May 27, 2020 4:21 AM
  • You're welcome, Mohammad!  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 27, 2020 4:57 AM
  • Hi Mohammad,

    It seems that your problem has been solved. If so, please click "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 27, 2020 6:14 AM
  • Hi Timon,

    Yes it is resolved. Thanks a lot for your support. Yes I will mark it as answer.


    Mohammad Nadeem Alam SME in Emirates NBD

    Wednesday, May 27, 2020 8:29 AM
  • Hi Timon,

    sorry to bother you again.

    I am getting the result as per expected, but there is one issue, we have not put any condition in our query. I mean to say need to group when other column value like account and ICP should also same, if account and ICP is different then it should go in different row. we need to put group by Text but putting the condition that account, ICP value should also same.if it is different it should go in different rows.  Can you please help how to put and condition to meet the desired result.


    Mohammad Nadeem Alam SME in Emirates NBD


    Monday, June 8, 2020 4:21 PM
  • Hi Bonnie,

    sorry to bother you again.

    I am getting the result as per expected, but there is one issue, we have not put any condition in our query. I mean to say need to group when other column value like account and ICP should also same, if account and ICP is different then it should go in different row. we need to put group by Text but putting the condition that account, ICP value should also same.if it is different it should go in different rows.  Can you please help how to put and condition to meet the desired result.


    Mohammad Nadeem Alam SME in Emirates NBD


    Monday, June 8, 2020 4:21 PM
  • Hi Mohammad,

    Can you show us what your query currently looks like? It will help us make better suggestions.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, June 9, 2020 4:46 AM
  • Hi Bonnie,

    Thank you so much for your reply. In simple words I need to sum Debit and Credit for those  rows having same text value ,account ,ICP, etc. In case of text value is same , but account or ICP value is different, in that case it will be separate rows. need to sum only when other column values like account , ICP is also same.

    Please see below example. I should get the output as screen 1. where as I am getting output as screent shot 2.

    to just explain for text column value Recharge of Global Payroll, I am getting only two rows, where as it should be four rows as the ICP values are different


    Mohammad Nadeem Alam SME in Emirates NBD



    Tuesday, June 9, 2020 5:20 AM
  • Hi Bonnie,

    Query is same as mentioned above. I think if we can do group by ICP and Account column as well, should be able to get desired result. I am trying but not able to do that. how can we add more column in group by as of now it is only Text.

     string path = @"C:\WDIFS\AU\OUT\Journal.csv";
                DataTable Journal = new DataTable();
                Journal = ConvertCSVtoDataTable(path);
                DataTable dataTable1 = GetResDataTable(Journal, "Debit");
                DataTable dataTable2 = GetResDataTable(Journal, "Credit");
                dataTable1.Merge(dataTable2, false, MissingSchemaAction.Add);
                DataRow[] foundRows = dataTable1.Select("", "Text");
                DataTable dt = foundRows.CopyToDataTable();

     public static DataTable GetResDataTable(DataTable dataTable, string str)
            {
                var dt1 = dataTable.AsEnumerable()
                    .Where(r => r.Field<string>(str) != String.Empty)
                    

                    .GroupBy(r => r["Text"])
                   
                    .Select(g =>
                    {
                        DataRow row = dataTable.NewRow();
                        row["Year"] = g.First()["Year"];
                        row["Entity"] = g.First()["Entity"];
                       // row["Account"] = g.Key;
                        row["Account"] = g.First()["Account"];
                        row["Project"] = g.First()["Project"];
                        row["Location"] = g.First()["Location"];
                        row["CostCenter"] = g.First()["CostCenter"];
                        row["ICP"] = g.First()["ICP"];
                        //row["ICP"] = g.Key;
                        row["Employee"] = g.First()["Employee"];
                        row["Asset"] = g.First()["Asset"];
                        row["Currency"] = g.First()["Currency"];
                        row["BusUnit"] = g.First()["BusUnit"]; 
                        row["Debit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
                        if (row["Debit"].ToString() == "0")
                            row["Debit"] = DBNull.Value;
                        row["Credit"] = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));
                        if (row["Credit"].ToString() == "0")
                            row["Credit"] = DBNull.Value;
                        row["Text"] = g.Key;
                        row["Date"] = g.First()["Date"];
                        row["VoucherType"] = g.First()["VoucherType"];
                        row["SubProjectID"] = g.First()["SubProjectID"];
                        row["Activity"] = g.First()["Activity"];
                        return row;
                    }).CopyToDataTable();
                return dt1;
            }

    Mohammad Nadeem Alam SME in Emirates NBD

    Tuesday, June 9, 2020 10:48 AM
  • Hi Mohammad,

    You should be able to use something like this:

    .GroupBy(r => new { Text = r["Text"], ICP = r["ICP"], Account = r["Account"] })
    .Select(g=>
    {
        DataRow row = dataTable.NewRow();
        row["Year"] = g.First()["Year"];
        row["Entity"] = g.First()["Entity"];
        row["Account"] = g.Key.Account;
        row["Project"] = g.First()["Project"];
        row["Location"] = g.First()["Location"];
        row["CostCenter"] = g.First()["CostCenter"];
        row["ICP"] = g.Key.ICP;
        row["Employee"] = g.First()["Employee"];
        // etc. etc.
        row["Text"] = g.Key.Text;
        // etc.etc.
    })
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, June 9, 2020 3:07 PM
  • Hi Bonnie,

    I am very sorry for late response. Actually I was able achieve the same, exactly same way , as you described above.

    It is working fine now.

    Thanks a lot for your support.



    Mohammad Nadeem Alam SME in Emirates NBD

    Sunday, June 14, 2020 9:24 AM
  • You're welcome, Mohammad. I'm glad that you got it working!  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, June 15, 2020 4:40 AM
  • Hi Bonnie,

    Hope you are doing well. Sorry to bother you again. In process of summarization, I need one more logic to further summarize. Request your help for the same

    Based on the above logic, we took all Debit and grouped it based on Text, ICP, Account etc. and summed the Value for Debit

    Same way we took all the Credit and grouped it based on Text, ICP, Account etc. and summed all the value of Credit

    After that, we merged two tables Debit summarized table and Credit summarized table

    And we got the final data table having desired result.

    Now next need to do one more summarization, suppose we have got the final table having 100 or 1000 rows

    In that there are rows which has same column value of Text, ICP, Account etc. but  only difference in Debit and Credit value.

    For example out of 1000 rows two rows are same in all other columns only the Debit and Credit value differ, in that case I need to make 1 rows and net value will be either in Debit or Credit

    Like below example.

    Example – Before Summarization

    Entity

    Account

    Project

    Location

    Cost Centre

    ICP

    BusUnit

    Debit

    Credit

    Text

    10020

    36177

     

    DIV

     

     

    CORP

     

    21336.05

    Provision - Annual Leave Accrual

    10020

    36177

     

    DIV

     

     

    CORP

    4049.21

     

    Provision - Annual Leave Accrual

    Example – After Summarized

    Entity

    Account

    Project

    Location

    Cost Centre

    ICP

    BusUnit

    Debit

    Credit

    Text

    10020

    36177

     

    DIV

     

     

    CORP

     

    17286.84

    Provision - Annual Leave Accrual

    As you can see in first table all columns are same except Debit and Credit values and Credit values is greater that Debit value. So net value of credit will be 21336.05-4049.21=17286.84

    Same way out of 1000 rows, if other rows falling in the same way, need to do the same thing.


    Wednesday, June 17, 2020 6:45 AM
  • Hi,

    Try the following code, it should meet your requirements.

      var dt1 = dataTable.AsEnumerable()
                 .GroupBy(r => r["Account"])
                 .Select(g =>
                 {
                     DataRow row = dataTable.NewRow();
                     row["Account"] = g.First()["Account"];
                     row["Project"] = g.First()["Project"];
                    // ...
    
                     decimal Debit = g.Sum(r => Decimal.Parse(r.Field<string>("Debit") == string.Empty ? "0" : r.Field<string>("Debit")));
                     decimal Credit = g.Sum(r => Decimal.Parse(r.Field<string>("Credit") == string.Empty ? "0" : r.Field<string>("Credit")));
                     if (Debit-Credit >= 0)
                     {
                         row["Debit"] = Debit - Credit;
                         row["Credit"] = DBNull.Value;
                     }
                     else
                     {
                         row["Credit"] = Credit - Debit;
                         row["Debit"] = DBNull.Value;
                     }
                     return row;
                 }).CopyToDataTable();
    

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 17, 2020 7:48 AM
  • Hi Timon,

    Thanks a lot for your quick reply. I will check and let you know the outcome. just by seeing the code how we making sure other colums value is same. can we put where condition like rwos1. account= rwos2.account same for ICP,project,location etc.


    Mohammad Nadeem Alam SME in Emirates NBD

    Wednesday, June 17, 2020 8:14 AM
  • Hi,

    Sorry, I haven't considered this before.

    If the account columns in these two rows may be different, the fieldName in GroupBy should be modified to group by.

    In linq, you can use the following code to assign a specific row‘s value to a column of the result.

    // g.ElementAt(index)
    row["columnName"] = g.ElementAt(1)["columnName"];

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 17, 2020 8:36 AM
  • Hi Timmon,

    Thanks a lot for your quick response. My requirement is if there is 1000 in data table. we need to touch those rows which are same in terms value except debit and credit. For example if two rows are same for  Text1 (same in terms of all column value except Debit and Credit value). then these two rows need to make 1 and net value for debit or credit  will based on above provided logic. same way like for text5 two are are same except Debit and credit values then need to make 1. Rest rows will be as it is. because, we have already done group  by.

     Hope I am able explain my requirement.


    Mohammad Nadeem Alam SME in Emirates NBD

    Wednesday, June 17, 2020 9:59 AM