Joining two data table in which rows of a certain column are matched

Learning Hub
1

Hi Guys,

I have DTa and DTb

DTa is consist of:-
Supplier_Code / Supplier / PO_Number / Description / Date

DTb is consists of
Supplier_Code / Supplier / Email

I’d like to append Email in DTb to DTa as per the list of Supplier_Code in DTa

For example,

DTa has a list of Supplier_Code
A,D, H

DTb has a list of Supplier_Code
A,B,C,D,E,F,G,H,I,…

I only need email contacts of A,D,H.

I tried to use Join Data Table with a condition when Column 0 in DTa is same as Column 0 in DTb.
But this doesn’t work as the result doesn’t even show Email data.

What is the easiest and shortest way to make this work without building another Data table?

Thank you!

2

Hi @Jeong-min.lee - did you try the Lookup activity?

3

pls try below

Datatable Out_Matched_Data = In_DataTable1.AsEnumerable().Where(Function(row) In_DataTable2.AsEnumerable().Select(Function r.Field(Of String)(“ColumnA”)).Any(Function(x) x = row.Field(Of String)(“ColumnA”))).CopyToDataTable()

OR

In_DataTable1.AsEnumerable().Where(Function(row) In_DataTable2.AsEnumerable().Select(Function r.Field(Of String)(“ColumnA”)).Any(Function(x) x = row.Field(Of String)(“ColumnA”))).CopyToDataTable()

4

Yes, I’m trying to search how to utilize Lookup function, but to be honest, I still don’t get the concept of Lookup… :sweat_smile:

5

Try going through: Lookup Data Table
Includes an example.

6

안녕하세요, 한글로 답변드립니다.
Datatable - Join Datatable을 사용하시면 될 것 같습니다.

하기 스크린샷 참고해주시고, 추가 문의사항 있으시면 답변드리겠습니다.

  1. DTa
    image

  2. DTb(More Row)
    image

  3. Join DataTable(Left)
    image

  4. Result
    image

특정컬럼만 남기고 싶은경우.

  1. Filter Datatable
    image

  2. Result
    image

8

is this using Assign Function?

9

@Jeong-min.lee - yes pls… use assign activity for this

10

안녕하세요

답변감사합니다!

근데 제가 Join Data Table activity 넣었을 때,
아래와 같이 DTa 는 그대로 데이터가 표시되는데 DTb는 Column 이름만 나오네요…
뭐가 문제일까요? ㅠㅠ

Column A ~ E - DTa
Column F,G,H - DTb

image

11

Raw Data와 코드를 보면 좋겠지만,
G컬럼의 Supplier Code가 Return이 안된걸로 봐서, Supplier Code로 두 데이터를 매핑을 못한것 같습니다.

Join Table조건 거실때,
DTa “Supplier_Code” 와 DTb “Supplier Code”(언더바 없음) 의 오타가 나진 않았는지 확인해 보시면 좋을 것 같습니다.

12

DTa, Supplier Code에 끝에 Space가 들어있어서 매칭을 못했네요 ㅠㅠ…

아무쪼록 해결했네요 감사합니다

1 Like