Compare datatable columns and get the next column value

Hi,

I want to lookup Number column from Dt1 in Dt2 and get the relevant value of Status from Dt2 if the condition matches. I want to get this activity done using select function as each datatable has 80k+ rows in it and the regular for each or lookup datatable activity is taking a lot of time.

Hi @Archana_Y

Refer to the link below similar to yours!

Regards

I want to lookup Number column from Dt1 in Dt2 and get the relevant value of Status from Dt2 if the condition matches

Hi @Archana_Y

I am not so much familiar in writing LINQ !

Looping @ppr @Yoichi to have a look on this.

Regards

@Archana_Y
just provide some sample data for us. Based on this we will check for a solution approach

2 Likes

ClearTaxDownload_09.12.2021 - Copy.xlsx (9.8 KB)
ClearTaxDownload_10.12.2021 - Copy.xlsx (8.3 KB)
document dated 10.12.2021 is Dt1 and 9.12.2021 is Dt2.

need to check if the match status in Dt1 is missing in 2a, then have to get the respective Seller GSTIN (PR) value and find that value in Dt2. if the value is found then have to see if the Match status in Dt2 is same as that of Dt1. In case the status matches then have to store that value

I was trying to get the match value in this way from Dt2:

LastDayData.select("[Document Number (PR)]=’+MS+’")(1).ToString

where lastdayData is from 09.12.2021 and MS is Seller GSTIN (PR) value from Dt1 ( 10.12.2021)

We could rewrite the requirement

  • Dt1.“Match Status” = “Missing in 2A”, use same Row Column"Seller GSTIN (PR)" value - sg1
  • Check for sg1 in Dt2.“Seller GSTIN (PR)” for same value as sg1

32AAACM7493G1ZG used as sg1 will find many rows in dt2 as there are duplicates in dt2
Same also for dt1 - there are duplicates for the sg1 value

So, here we do have some doubts if the requirement description is covering the intention. To make it short. We recommend to resharpen the requirements and to define more specific what is to achieve by also including alternates scenarios (sg1 values found many times with different Match status in dt2)

However also have a check if using a join (Join datatable activity) on the deduplicated datatables can be used:

I just want to know if there is a way via select function to lookup value in a datatable (Dt2) and get the relevant value from the datatable (Dt2)

Example:

Dt1 /Source Datatable

image

Dt2/ Destination datatable

image

I need to lookup value Seller GSTIN (PR) from Dt1 in Dt2 and if the case matches then get the value of respective Match Status from Dt2