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

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?

Answer 1

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

Answer 2

User475983607 posted

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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