UiPath DataTable Match

Help Studio
, ,
1

Hi all,

I am stuck with RPA scenario and seeking your support.

Question:

I have UiPath data table called “Age_DT” and data table having following columns and data

age_range | Rate
18-60 | 1%
18-65 | 1.5%
18-70 | 2%
18-75 | 2.5%

And Also I have another table called “cus_data” which having following column and data;

cusName | AGE
Mr. ABC | 50
Mr. DEF | 66
Mr. GHI | 74

Okay. Now I want to iterate my "cus_data " data table with age column compare with “Age_DT” data table “age_range” column and check whether if the customer age is falling to its range if so what is the rate ? . Also if cusotmer age is not in the “Age_DT” range I want to add the customer name into separate data table (what ever DT name but need to sorted the record)

Example :
Match record

Mr. ABC AGE is “50” and he is covered under “Age_DT” data table 1st row which is "18-60 " age range

Hope you understand my question . And seek your support on this regard.

2

HI,

Can you try the following sample?

image
image1127×778 37.9 KB

Sample
Sample20231218-1L.zip (10.2 KB)

Regards,

3

Hi @Duleepa_Krishan ,
We have 2 popular ways

  1. using the activity with 2 loop
    with “If” to compare then find match

  2. using LINQ

Regards,

4

@Yoichi , Thanks for the solution this works perfectly fine until u provide matchable age data. Exampe if you pass some person age is not in the given range ,

Mr.JKL 17 flow get error .

Also as I mentioned in my first requirement I need to store the data which are not matching in a separate data Table.

Seek your advice and appreciate your support given

5

can you please share a scenario

6

HI,

How about the following?

image
image1246×1914 123 KB

Sample20231218-1L (2).zip (10.6 KB)

Regards,

1 Like
7

@Yoichi Thanks ! that was perfect !. If you don’t mind can you please explain what you did was there ? . Cz I would love to learn

8

HI,

If you don’t mind can you please explain what you did was there ?

dict = dtCus.AsEnumerable.ToDictionary(Function(r) r("cusName").ToString,Function(r) dtMaster.AsEnumerable.Where(Function(r2) CInt(r2("age_range").ToString.Split({"-"c}).First())<=CInt(r("AGE").ToString) AndAlso CInt(r2("age_range").ToString.Split({"-"c}).Last())>CInt(r("AGE").ToString)).FIrstOrDefault)

This expression create dictionary which key is cusName and value is datarow of datatable for rate. If there is no range which matches his/her age, Nothing(null) is set.

dtCus.AsEnumerable.ToDictionary is LINQ method to create dictionary

Function(r) r("cusName").ToString is set each item of cusName column as key of the dictionary. (1st argument of ToDictionary method)

Function(r) dtMaster.AsEnumerable.Where(Function(r2) CInt(r2("age_range").ToString.Split({"-"c}).First())<=CInt(r("AGE").ToString) AndAlso CInt(r2("age_range").ToString.Split({"-"c}).Last())>CInt(r("AGE").ToString)).FIrstOrDefault

The above returns the first datarow which is matched “age_range”. It’s checked with split value by “-”. (2nd argument of ToDictionary method)

Next, filter null value using the following expression.

dict = dict.Where(Function(kv) kv.Value isnot Nothing).ToDictionary(Function(kv) kv.Key,Function(kv) kv.Value)

Unmatched record is extracted by the following LINQ expression using the above dictionary. (filter records which doesn’t exist in keys of the dictionary)

dtCus.AsEnumerable.Where(Function(r) not dict.Keys.Contains(r("cusName").ToString)).ToArray

Hope this helps you.

Regards,

1 Like
9

Thanks @Yoichi for the explanation and the support you have given. really appreciated.!

10

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