How merge two data tables based on Index value using linq query

Help Activities
, ,
1

Hi,

I am having two tables and contain data. I need to merge the two tables based on columns and final dt count should equals to dt1.

dt1:

IndexValue PO date
1 abc 1/29/2025
2 xys 1/30/2025
3 gjh 1/31/2025
4 jgj 2/1/2025
5 hi 2/2/2025
6 yio 2/3/2025
7 uiuo 2/4/2025
8 hk 2/5/2025

Dt2:

IndexValue Po date company
1 abc 1/29/2025 345
5 yio 2/3/2025 567

Output:

IndexValue PO date company
1 abc 1/29/2025 345
2 xys 1/30/2025
3 gjh 1/31/2025
4 jgj 2/1/2025
5 hi 2/2/2025 567
6 yio 2/3/2025
7 uiuo 2/4/2025
8 hk 2/5/2025

TIA

2

Did you try Merge Data table activity?

3

output data count coming higher than the dt1.
I need linq query for
in prod data is very huge

4

Try Join Data table also. with Left join properties. It is always better to use inbuilt activities before proceeding with Linq query. Try with small count to see if it is working fine before going with full data table. This will help us to segregate the issue better

image
image803×342 29.7 KB

5

@Manaswini_UI ,

Tried asking any LLM? Try this one.

outputDataTable = (From row1 In dt1.AsEnumerable()
                   Group Join row2 In dt2.AsEnumerable()
                   On row1.Field(Of String)("IndexValue") Equals row2.Field(Of String)("IndexValue")
                   Into Group
                   From row2 In Group.DefaultIfEmpty()
                   Select outputDataTable.LoadDataRow(New Object() {
                       row1.Field(Of String)("IndexValue"),
                       row1.Field(Of String)("PO"),
                       row1.Field(Of DateTime)("date"),
                       If(row2 IsNot Nothing, row2.Field(Of String)("company"), Nothing)
                   }, False)).CopyToDataTable()
6

@Manaswini_UI

A simple left join should do the job and no linq is needed

If you see extra columns just use filter datatable and remove the columns you dont need

Cheers

7

getting more number columns than dt1 using left join activity

8

@Manaswini_UI

Use filter datatable

Cheers

9

which condition do we need to apply to filter?

10

getting extra rows. Unable to filter using specific condition

11

@Manaswini_UI

Extra rows would not come…there would be extra columns…if you open wizard…then you would see a columns tab…just give the names you need and select keep

Cheers

12

am trying to join using index value using join activity in output dt all columns coming but data picking from only dt1. Could you please advise on this.

13

@Manaswini_UI

Can you show what you tried and how output looks

Ideally you would get both the columns

Also make sure index is of same type (column type) in both tables

Cheers

14

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