I have two tables and i have to extract only the missing values

Help Studio
, ,
1

I have 2 datatables with multiple column of same coluumn names but differnt orders

my input datatable has data with few missing values
my master datatable has all the data but i want to extract only the missing values of dt1 and paste in dt1 missing cell

below is my input datatable
image

below is my master datatable
image

my output should be
image

the common key between these two datatable is column “key”

Thiis is a sample dt
my dt has several columns and rows
so i have to fill all the missing cell values only of dt1 by fetchiing it from master dt

2

@Chirag12

One approach would be

  1. For each row in datatable or for eachr ow in excel anything would wotk on the first dt1 where you want to fill the data
  2. Inside that use filter datatable activity and filter the dt2 on key column with currentRow("Key").ToString
  3. Use assign activity with currentRow("RequiredColumn") = filtereddt.Rows(0)("RequiredColumn").ToString

Hope this helps

Cheers

3

Hi

Hope the below steps would help you resolve this
Follow up and add the activities as mentioned in sequence

  1. Say you have dt1 and dt2 as two datatable

  2. Use a ASSIGN activity like this

counter = 0

Where counter is a variable of type int32

  1. Now use a WHILE Loop activity and mention the condition like this

counter < dt1.Rows.Count

  1. Inside the while loop use a assign activity like this

Str_columnname = dt1.Columns(counter).ColumnName.ToString

  1. Next to this assign activity inside while loop use a FOR EACH ROW datatable and pass dt1 as input

  2. within this FOR EACH ROW loop use a IF activity with condition like this

String.IsNullOrEmpty(CurrentRow(counter).ToString.Trim)

if true it goes to then block or to else block
Keep the else block empty

  1. In THEN block, use a assign activity like this

CurrentRow(counter) = dt2.Rows(dt1.Rows.IndexOf(CurrentRow))(str_columnname.ToString).ToString

  1. Now outside this for each row loop but still within the while loop use a assign activity like this

counter = counter + 1

That’s it at the end of the loop dt1 will have all the value you want

Hope this helps

Cheers @Chirag12

ese properties