How to match and mapping values in an excel

Hi Everyone

I have a mapping file with three columns
Eg:

App Role in website Role in Record
Dell Read Only Read Only
Dell Write Write Only
Upstock [Upstock_ADMIN, Java_USER] CR3
Upstock [POST_ADMIN, ACCESS_ADMIN, TINCOMP, upstock_ADMIN, UI_ADMIN, Comp_ADMIN, ] VR3

We get Column1(App) and Column2(Role in website) values as inputs
P.s (Column2) values can be any order and any numbers of words

Based on these to inputs need to get column3(Role in record value)

@ppr @Palaniyappan please provide ur valuable inputs

Thanks in advance

Unfortunately the request is unclear and maybe you can elaborate more on this

sounds like a filter use case

Thankyou for the response

Basically inputs app and role in snow are from SNOW
I extract the “app” (column1) and “role in website” (column2) from SNOW description.

Then match the extracted “role in website” with column2 of the mapping file(table as above) and extract the corresponding column3 value (“Role in record”) value from mapping sheet.

maybe better you share a full sample data and its details
still, it looks like a filter case

Assign activity
strRoleInRecord =

(From d in dtMapping.asEnumerable
Where d("App").toString.Trim.Equals(yourAppKeyVar)
Where d("Role in website").toString.Trim.Contains(yourRoleKeyVar)
Select s = d("Role in Record").toString.Trim).FirstOrDefault()

Thankyou for the solution,
Its works fine, but there is a hindrance that if
Eg: Column2 : [POST_ADMIN, ACCESS_ADMIN, TINCOMP, upstock_ADMIN, UI_ADMIN, Comp_ADMIN]
InputRoleVar : [POST_ADMIN, TINCOMP, upstock_ADMIN, UI_ADMIN, Comp_ADMIN, ACCESS_ADMIN]

The above query is not working if values are shuffled or number of words are less
Ps : values are not in static order or no. of words.

@Kavya_S

Then you can try a slight variation to above query like this

(From d in dtMapping.asEnumerable
Where d("App").toString.Trim.Equals(yourAppKeyVar)
Where yourRoleKeyVar.ToLower.Split(","c).Except(d("Role in website").toString.ToLower.Split(","c)).Count=0
Select s = d("Role in Record").toString.Trim).FirstOrDefault()

Cheers

1 Like

it is all about requirment definition and sample data

When

contains ACCESS_ADMIN

and

is the input then it looks like we want to search for roles from input in the mapping.

So we split the input roles on ,

(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()

and also recommended to externalize to variables when it is possible

UPD1 - included [ ] trim

UPD2 - fixed based on update from:

1 Like

This solution is working.
Thankyou Sir!!

1 Like

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