How to match and mapping values in an excel part2

Help Studio
1

Continuing the discussion from How to match and mapping values in an excel:

Sorry for ReOpening the Topic
@ppr There is an issue for the below set of input this solution isn’t working

App Role in website Role in Record
RM [SUPPORT 112, SUPPORT 932, SUPPORT 411, SUPPORT 532, SUPPORT 162, SUPPORT 200, SUPPORT 521, SUPPORT 526, SUPPORT 213, SUPPORT 528, SUPPORT 161, SUPPORT 171, SUPPORT 531, OFXMFA 100, SUPPORT 165, SUPPORT 523, OFXMFA 0, SUPPORT 182, SUPPORT 212, SUPPORT 529, SUPPORT 160, SUPPORT 180, SUPPORT 170, SUPPORT 536, SUPPORT 402, SUPPORT 524, SUPPORT 183, SUPPORT 116, SUPPORT 181, SUPPORT 300, SUPPORT 534, SUPPORT 403, SUPPORT 163, SUPPORT 527, SUPPORT 522, SUPPORT 533, SUPPORT 530, SUPPORT 115, SUPPORT 211, SUPPORT 210, SUPPORT 520, SUPPORT 525] MC Consultant
RM [IA 105, SUPPORT 121, SUPPORT 404, SUPPORT 107, SUPPORT 406, IA 0, SUPPORT 407, SUPPORT 409, SUPPORT 100, SUPPORT 130, SUPPORT 1, IA 100, SUPPORT 106, SUPPORT 0, SUPPORT 167, SUPPORT 131, IA 102, SUPPORT 111, SUPPORT 410, SUPPORT 401, SUPPORT 535, SUPPORT 110, SUPPORT 120, SUPPORT 117, IA 103, SUPPORT 930, SUPPORT 601, SUPPORT 408, SUPPORT 602, SUPPORT 412, SUPPORT 931, SUPPORT 400, SUPPORT 123, SUPPORT 190, SUPPORT 114, SUPPORT 108] consultant

InputRoleVar : [SUPPORT 400, IA 102, SUPPORT 601, IA 105, SUPPORT 407, SUPPORT 131, IA 0, SUPPORT 106, SUPPORT 410, SUPPORT 408, SUPPORT 0, SUPPORT 930, SUPPORT 117, SUPPORT 100, SUPPORT 412, IA 100, SUPPORT 107, SUPPORT 167, SUPPORT 123, SUPPORT 931, SUPPORT 602, SUPPORT 409, SUPPORT 406, SUPPORT 120, SUPPORT 110, SUPPORT 190, SUPPORT 1, SUPPORT 108, SUPPORT 404, SUPPORT 111, SUPPORT 114, SUPPORT 401, SUPPORT 130, SUPPORT 535, IA 103, SUPPORT 121]

Instead of getting output as consultant I am getting MC Consultant

Thankyou in advance @ppr

3

Support 1 from inputRoles is wrongly matching row 1, when doing a string Contains.

Lets rewrite to the following:

Assign activity
strRoleInRecord =

(From d in dtMapping.asEnumerable
Where d("App").toString.Trim.Equals(yourAppKeyVar)
Let rls = InputRoleVar.Trim("[]".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray
Let wrl = d("Role in website").toString.Trim(" []".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray
Where rls.Intersect(wrl).Any()
Select s = d("Role in Record").toString.Trim).FirstOrDefault()
1 Like
4

This is working, Thankyou
If possible please explain the logic of the query its excellent, Thanks

5

it is about LINQ
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

(From d in dtMapping.asEnumerable

loop over all rows, refer with d on the looped row

Where d("App").toString.Trim.Equals(yourAppKeyVar)

filter/let pass where App col value = yourAppKeyVar

Let rls = InputRoleVar.Trim("[]".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray

split InputRoleVar on “,” + remove on the borders and trim the splits - refer to it with rls (used like a local variable)

Let wrl = d("Role in website").toString.Trim(" []".ToCharArray).Split(","c).Select(Function (x) x.Trim).ToArray

similar to rls do the same with wrl for the Role in Website column value

Where rls.Intersect(wrl).Any()

with intersect find the common items from wrl and rls and pass the filter if any common items are present

Select s = d("Role in Record").toString.Trim).FirstOrDefault()

when passing all filters, select the Role in Record value
use from all rows passing the filters the first occurence or the string default value if no row matches the filters (empty result)

6

so the topic can be closed
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum