Add column values from one datatable to another basis a condition

Help Studio
, ,
1

Hi ,

I have two datatabales, Dt1 and Dt2.

I want to check if columns “Seller Name and Doc No.” from Dt1 are matching with “Party Name and Bill No,” from Dt2, then I want to get the Acc No. and Date columns details from Dt2 and paste it in Dt1.

Since both Dt1 and Dt2 have excel rows exceeding 80k, I want to use either select, enumerable or linq query to reduce execution time. Please help in this regard!

2

Adding @ppr @Yoichi @kirankumar.mahanthi1 @Sudharsan_Ka

3

Hi @Archana_Y

first you have to create a datatable for output using build data table activity Ex OutPut_DT

OutPut_DT= (
                       From row1 in Dt1
                       Join  row2 in Dt2
                       On Cstr(row1("Seller Name")) = Cstr(row2("Party Name")) and Cstr(row1("Doc No")) = Cstr(row2("Bill No"))
                       Select OutPut_DT.Rows.Add((row1("column name1"),row1("column name2"),..row1("column nameN"),row2("column name1"),row2("column name2))
                       ).CopyToDataTable

Hopes it solves your issue

Thanks
Robin

4

The column names and the number of columns in both the datatables are not same. Will join functionality work?

5

yes it will work

row1(“column name1”),row1("column name2),…row1(“column nameN”),row2(“column name1”),row2("column name2)**

just replace the column names of both datatables as per your document

6

inner join will only handle the common rows between dt1,dt2
in case of Acc No. and Date columns details from Dt2 should be blank within the result for rows not found in dt2 we would use a left join:

we would recommend to start with the join datatable activity and post processing the join result by e.g removing unneeded columns

In case of a LINQ is wished, then please clear the requirement of handling unmatched rows within dt2

7

Hi,

The rows which are not matching in dt2, they can be ignored completely. I only want the Acc no and date column values from the rows which match.

8

There is an error , says Equals Expected

image
image1366×768 95.7 KB

9

Just replace = sign into Equals

like row1(2)Equals row2(2)
refer below image

image

hope it solves your issue

10

it is mandatory of using the EQUALS and both dt within the join condition like
dtLeft(YourLJCol).toString Equals dtRight(YourRJCol).toString

it is recommended to us AsEnumerable like (From d1 in dt1.AsEnumerable

1 Like
12

Hi,

In case I want the Accno and date to return empty fields in case it doesn’t match , then how can I refine the query?

13

Hi @Archana_Y

if the second datatable value is empty
you have to use like the below query
if the data is null it will return empty rows

OutPut_DT= ( 
From row1 In DT
Group Join  row2 In Out_Dt2
On CStr(row1("Seller Name")) Equals CStr(row2("Party Name")) And CStr(row1("Doc No")) Equals CStr(row2("Bill No")) Into grp = Group
From g In grp.DefaultIfEmpty
Select Out_DT.Rows.Add(row1("column name1"),row1("column name2"),row1("column nameN"),If(IsNothing(g),Nothing,g("column name1")),If(IsNothing(g),Nothing,g("column name2")))
   ).CopyToDataTable

Hope it helps you

Thanks,
Robin

14

So here Select.Dt.Add will still have row1 and row2 columns and the if condition will only contain row 2 columns as empty?

15

yes that is the functionality of left join

16

if the row 2 value is empty that particular cell is emptied or else data will be added

17

is this query correct then:

(From row1 In Data.AsEnumerable
Group Join row2 In LastDayData.AsEnumerable On CStr(row1(“Seller GSTIN (PR)”)) Equals CStr(row2(“Party GSTIN”)) And CStr(row1(“Document Number (PR)”)) Equals CStr(row2(“Bill No.”)) Into grp = Group
From g In grp.DefaultIfEmpty
Select FinalTxtDt.Rows.Add(row1(“Match Status”).ToString,row2(“Accounting Doc.”).ToString,row2(“Posting Date”).ToString.Trim,If(IsNothing(g),Nothing,g(“Accounting Document”)),If(IsNothing(g),Nothing,g(“Posting date”))).CopyToDataTable

Expected Output: All the rows from Dt1 and Acc no and date from Dt2(if match happens) and empty rows from Dt2 (Acc no and date) if there is no match

18

Yes extactly

19

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.