Correct expression for matching and output?

Help Studio
, ,
1

Hi guys,

Is the below expression correct?

(From row In dt1
Join row2 In dt2
On row(“COLLECTION_CONTROL_KEY-Fin”).ToString().Trim() Equals row2(“COLLECTION_CONTROL_KEY-Col”).ToString().Trim()
Let key = If(row(“COLLECTION_CONTROL_KEY-Fin”).ToString() = row2(“COLLECTION_CONTROL_KEY-Col”).ToString(),“Match”,“No Match”)
Let dates = If(row(“EFFECTIVE_TRANSACTION_DATE_KEY-Fin”).ToString() = row2(“DATA_FROM_DATE-Col”).ToString(),“Match”,“No Match”)
Let amount = If(row(“AMOUNT_Fin”).ToString() = row2(“ORIG_EXPECTED_AMOUNT-Col”).ToString(),“Match”,“No Match”)
Let ra = New Object(){row(“COLLECTION_CONTROL_KEY”), row2(“Key”), key}
Let ra = New Object(){row(“COLLECTION_CONTROL_KEY”), row2(“Dates”), dates}
Let ra = New Object(){row(“COLLECTION_CONdt2dt1dt1dt1TROL_KEY”), row2(“Amounts”), amount}
Select dtResult.Rows.Add(ra)).CopyToDataTable

1 Like
2

@Nyx

(From row In dt1
Join row2 In dt2
On row("COLLECTION_CONTROL_KEY-Fin").ToString().Trim() Equals row2("COLLECTION_CONTROL_KEY-Col").ToString().Trim()
Let key = If(row("COLLECTION_CONTROL_KEY-Fin").ToString() = row2("COLLECTION_CONTROL_KEY-Col").ToString(),"Match","No Match")
Let dates = If(row("EFFECTIVE_TRANSACTION_DATE_KEY-Fin").ToString() = row2("DATA_FROM_DATE-Col").ToString(),"Match","No Match")
Let amount = If(row("AMOUNT_Fin").ToString() = row2("ORIG_EXPECTED_AMOUNT-Col").ToString(),"Match","No Match")
Let ra = New Object(){row("COLLECTION_CONTROL_KEY"), row2("Key"), key, row("COLLECTION_CONTROL_KEY"), row2("Dates"), dates, row("COLLECTION_CONdt2dt1dt1dt1TROL_KEY"), row2("Amounts"), amount}
Select dtResult.Rows.Add(ra)).CopyToDataTable()
3

Hi @Nyx

Try the below syntax:

(From row In dt1
Join row2 In dt2
On row("COLLECTION_CONTROL_KEY-Fin").ToString().Trim() Equals row2("COLLECTION_CONTROL_KEY-Col").ToString().Trim()
Let key = If(row("COLLECTION_CONTROL_KEY-Fin").ToString() = row2("COLLECTION_CONTROL_KEY-Col").ToString(),"Match","No Match")
Let dates = If(row("EFFECTIVE_TRANSACTION_DATE_KEY-Fin").ToString() = row2("DATA_FROM_DATE-Col").ToString(),"Match","No Match")
Let amount = If(row("AMOUNT_Fin").ToString() = row2("ORIG_EXPECTED_AMOUNT-Col").ToString(),"Match","No Match")
Let ra = New Object(){row("COLLECTION_CONTROL_KEY"), row2("Key"), key}
Let rb = New Object(){row("COLLECTION_CONTROL_KEY"), row2("Dates"), dates}
Let rc = New Object(){row("COLLECTION_CONTROL_KEY"), row2("Amounts"), amount}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Hope it helps!!

1 Like
4

Thank you so much for the quick response

2 Likes
5

@Nyx

You’re welcome.
Happy Automation

1 Like
6

Will this add the rb and rc to the datatable?

1 Like
7

@Nyx

Yes.Try and check

1 Like
8

Im getting issue:

Select dtResult.Rows.Add(ra)).CopyToDataTable = Rows is not a member of string

Do I need to add Rows as variable to string?

1 Like
10

Found my mistake, dont worry

2 Likes
11

In Addition to the above

When working with a LINQ statement never omit the appended AsEnumerable() when working with DataTables

Statement started with an inner join

Left: COLLECTION_CONTROL_KEY-Fin = Right: COLLECTION_CONTROL_KEY-Col

As only the matching rows will be passed to the further processing there will no be Non-Matchers which makes the below obsolete

Before stepping in other possible correction needs we recommend to focus on the overall goal. Maybe you can share it with us along with some sample data

Also have a look here for the different join types:

1 Like
12

Hi,

The objective is to match the values in these 3 columns, to obtain the exceptions:

COLLECTION_CONTROL_KEY-Fin → COLLECTION_CONTROL_KEY-Col
EFFECTIVE_TRANSACTION_DATE_KEY-Fin → DATA_FROM_DATE-Col
AMOUNT_Fin ->ORIG_EXPECTED_AMOUNT-Col

CollectionControlKey
The values from this column acts as a unique identifier, it will either be in both or in the one but not the other. It can also be duplicated.

Here is the sample data →
Financials 20240122.xlsx (1.7 MB)
PRD_Collections_Delta_20240123.xlsx (6.5 MB)

The next step I want to pull the columns for both matched and unmatched rows to one results datatable for traceability.

Results.xlsx (8.8 KB)

Can I achieve these results with and Assign; Invoke Code or do I have to use a For each?

The data received are huge files, the solutions needs to run quickly

1 Like
13

Please help, how to add the 3 objects to the table with:

Select dtResult.Rows.Add(ra)).CopyToDataTable

(From row In dt1.AsEnumerable
Join row2 In dt2.AsEnumerable
On row(“COLLECTION_CONTROL_KEY-Fin”).ToString().Trim().ToUpper() Equals row2(“COLLECTION_CONTROL_KEY-Col”).ToString().Trim().ToUpper()
Let cckey = If(row(“COLLECTION_CONTROL_KEY-Fin”).ToString() = row2(“COLLECTION_CONTROL_KEY-Col”).ToString(),“Match”,“No Match”)
Let dates = If(row(“EFFECTIVE_TRANSACTION_DATE_KEY-Fin”).ToString() = row2(“DATA_FROM_DATE-Col”).ToString(),“Match”,“No Match”)
Let amount = If(row(“AMOUNT-Fin”).ToString() = row2(“ORIG_EXPECTED_AMOUNT-Col”).ToString(),“Match”,“No Match”)
Let ra = New Object(){row(“COLLECTION_CONTROL_KEY-Fin”), row2(“COLLECTION_CONTROL_KEY-Col”), cckey}
Let rb = New Object(){row(“EFFECTIVE_TRANSACTION_DATE_KEY-Fin”), row2(“DATA_FROM_DATE-Col”), dates}
Let rc = New Object(){row(“AMOUNT-Fin”), row2(“ORIG_EXPECTED_AMOUNT-Col”), amount}
Select dtResult.Rows.Add(ra)).CopyToDataTable

1 Like
14

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