locked
Match the data tables values from data-table 1 and data-table 2 and bring the match values into another data-table 3 using LINQ OR Some other trick RRS feed

  • Question

  • User-471420332 posted

    I have two data-table like below

    DataTable dt = bl.Get_LKP_Tables(bo);
    DataTable dt2 = bl.Get_LKP_Tables(bo);

    Datatable dt have below value

    F1    F2   F3    F4
    Yes   No   Yes   No

    Data-table dt2 have below value

    F1    F2   F3
    Yes   Yes  Yes

    I want match output like below into Data-table dt3

    F1   F3
    Yes  Yes 

    How to match the column name with values and no need to match static by adding column names like f1 or f2 or f3 or f4 conditions because in feature more field will add then it will be issued a base on the column name and value match only need to check?

    Thursday, March 28, 2019 2:12 PM

All replies

  • User753101303 posted

    Hi,

    And you have only a single row or you can have multiple rows?

    For now it seems you want to basically copy to a 3rd table columns having both "Yes" in the first 2 tables (or maybe having the same value ? not sure what happens if F2 is "No" in the 2 first tables, it is also ignored or a F2 column is created with the "No" value).

    Also sometimes it's best to have a big picture. As you talk about adding more fields it could be also some kind of normalization problem that would easier to handle anyway way (for example if F1 is for January, F2 for February etc...)

    For example it could be something such as :

    SELECT t1.* FROM (VALUES('F1','Yes'),('F2','No'),('F3','Yes'),('F4','No')) AS t1(id,value) -- Trick to create sample data without an actual table
    JOIN (VALUES('F1','Yes'),('F2','Yes'),('F3','Yes')) AS t2(id,value)
    ON t1.id=t2.id
    WHERE t1.Value='Yes' AND t2.Value='Yes'

    and you could add rows (rather than columns) as needed

    Thursday, March 28, 2019 5:03 PM
  • User475983607 posted

    The design makes little sense.  Can you explain the problem you are trying to solve?

    Thursday, March 28, 2019 5:08 PM
  • User-471420332 posted

    I have single row from both datatables... dt have one row and dt2 have one row.. it will not more than one row from both data tables

    And I don't want select query using linq or for each condition.. and please don't check condition like f1 or f2 or f3 or f4
    Thursday, March 28, 2019 7:42 PM
  • User-893317190 posted

    Hi mazhar khan india,

    If you accept to use  for each and if condition, you could use datatable's columns property. 

    Below is my code.

     protected void Page_Load(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                table.Columns.Add(new DataColumn("F1"));
                table.Columns.Add(new DataColumn("F2"));
                table.Columns.Add(new DataColumn("F3"));
                table.Columns.Add(new DataColumn("F4"));
                table.Rows.Add("Yes", "No", "Yes", "No");
                DataTable table2 = new DataTable();
                table2.Columns.Add(new DataColumn("F1"));
                table2.Columns.Add(new DataColumn("F2"));
                table2.Columns.Add(new DataColumn("F3"));
                table2.Rows.Add("Yes", "Yes", "Yes");

    List<string> table_columns = new List<string>(); List<string> table2_columns = new List<string>(); foreach (DataColumn item in table.Columns)// populate table_columns with all the columnnames in table { table_columns.Add(item.ColumnName); } foreach (DataColumn item in table2.Columns)// populate table_columns with all the columnnames in table2 { table2_columns.Add(item.ColumnName); } IEnumerable<string> common_cols= table_columns.Intersect(table2_columns);// get common column names in table and table2 DataTable final_table = new DataTable(); foreach (string com_col in common_cols) { if(table.Rows[0][com_col].ToString()=="Yes" && table2.Rows[0][com_col].ToString()=="Yes")// get all the yes column { final_table.Columns.Add(new DataColumn(com_col)); } } final_table.Rows.Add(new string[final_table.Columns.Count].Select(c=>"Yes").ToArray());// populate the first row with yes GridView1.DataSource = final_table; GridView1.DataBind(); }

    The result.

    Best regards,

    Ackerly Xu

    Friday, March 29, 2019 2:21 AM
  • User-471420332 posted


    Dear Ackerly Xu,

    In Future may be  values will change Yes/No to some other values can you pass some dynamic.

    See datatable dt and datable dt1 both have only one row every time, so you can match dynamic  because both have one row only with column name and value

    Hope you getting my point

    Friday, March 29, 2019 5:24 AM
  • User-893317190 posted

    Hi mazhar khan india,

    Not sure about your requirement, if you want to get dynamic value, just  change the value of yes to what you like.

    From your table , the value may be yes or no , in the future , the value may be a or  b.

    Then there is one problem, what value you want to filter?

    Yes or no , a or b.

    This depends on your requirement.

    If you want to get columns whose values are all yes, then you should specify yes, if you  want to get columns whose values are all a , you should specify a.

    In the code ,you should channg yes to no or whatever you like ,.

     
                foreach (string com_col in common_cols)
                {
                     if(table.Rows[0][com_col].ToString()=="write the value you want" && table2.Rows[0][com_col].ToString()=="write the value you want")// get all the yes column
                    {
                        final_table.Columns.Add(new DataColumn(com_col));
                    }
                }
                final_table.Rows.Add(new string[final_table.Columns.Count].Select(c=>"write the value you want").ToArray());// populate the first row with yes
    

    Best regards,

    Ackerly Xu

    Friday, March 29, 2019 7:01 AM