Extract and join data tables

Help StudioX
, ,
1

Hi All,

Need help with the following. I have two data tables dt_Items with columns Number, Item, Date, Amount and dt_Balance with columns Price, Order, Status, Start Date, Finish Date, Plan.
I want to get the rows that match the values in Balance column of dt_balance datatable with the Amount in dt_Items data table with all the column values in data tables to a dt_Filtered data tabe using a linq query or join data table activity. Please find the below tables for better understanding.

image
image1112×418 17.8 KB

Thanks in advance!

2

Hi @kavya_mamidishetti ,

try using the following linq to create dt_Filtered.

dt_Items.AsEnumerable().Join(dt_Balance.AsEnumerable(), Function(items) items.Field(Of String)("Amount"), Function(balance) balance.Field(Of String)("Price"), Function(items, balance) items.ItemArray.Concat(balance.ItemArray).ToArray()).CopyToDataTable()

if the datatype for column is decimal just switch the data type in linq

3

Hi,

You can use Join Data Table activity with Full Join.

image
image748×469 12.8 KB

Kind Regards,
Kardelen

4

Thank you for your response, It is giving me error - CopyToDataTable is not IEnumerable (OfObject())

5

Apologies, could you try this query instead

(From items In dt_Items.AsEnumerable()
                   Join balance In dt_Balance.AsEnumerable()
                   On items.Field(Of String)("Amount") Equals balance.Field(Of String)("Balance")
                   Select dt_Filtered.Rows.Add({items("Number").ToString, items("Item").ToString, items("Date").ToString, items("Amount").ToString, balance("Price").ToString, balance("Order").ToString, balance("Status").ToString, balance("Start Date").ToString, balance("Finish Date").ToString, balance("Plan").ToString})).CopyToDataTable()
6

Thank you for the response. It is giving all the rows.

7

Getting Object reference error even after initializing the data table

8

Hi @kavya_mamidishetti

Check the below zip file:

BlankProcess37.zip (59.3 KB)

Input:
dt_Items
image

dt_Balance
image

Output:
dt_Filtered

image
image790×140 5.97 KB

Let me know if you have any queries’

Regards

9

Hi @vrdabberu,
Thank you so much. It is working. Can you also provide the solution for non matching rows please? Please find below image for reference

image
image1212×448 19.2 KB

10

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