Filter Two DataTable

Help Studio
, , ,
1

I have two files in excel. One contains one column ( Dates) with several dates. The other contains 2 columns ( Date and number of rows). How can I get the value from number of rows from the second excel file, based on date from the first ? I have to take the value for all dates from the first file

2

hi @plamenov.plamen

Read both the tables using 2 different read range.
Apply a join based on Dates columns., if there are unique dates in both the tables.
Use a filter to get the row of the date that you want, and store number of rows in a variable.

Thanks

3

Hi @plamenov.plamen - Please check the attached sample workflow

  • Read the excel data to data table and follow the actions in the attached workflow
  • Here, I’ve taken data tables directly instead of reading the data from an excel

SampleWorkflow.zip (3.5 KB)

Picture1
Picture1742×755 25.2 KB

4

I just need the value from the second column of the second table.

5

Hi,

Do you need to update the first table as result? if so, how about the following?

image
image872×710 42 KB 

dict = dt2.AsEnumerable.ToDictionary(Function(r) r("Dates").ToString,Function(r) r("Number").ToString)

Then

CurrentRow("Number") = dict(CurrentRow("Dates").ToString)

Sample20230525-6L.zip (8.4 KB)

Regards,

6

I present something like for each in dt1 and in the loop "If the date from dt1 has it in dt2, then, take the value column2 from dt2. But I can’t handle the condition…

7

Hi,

Can you try the following sample?

image
image951×542 17.5 KB

Sample20230525-6LV2.zip (8.6 KB)

Regards,