Extract data from datatable based two variables; Roles(columns) and country(rows)

Help
1

Hello,
I’m trying to extract data from a datatable based on country and roles provided.
Country is based on user country - String variable
Roles are store in a list.
I’m using a “for each” to go thought the list of roles and assign the role to a string.

I now need to go through the datatable and get the right cell based on the role name (column) and the country (row) but i’m not sure how to do it.

Can you please help me out?

2

@abuzzi
Great post and great start from you :slight_smile: Welcome to the community,

If you want to search for columns and rows in the excel, you need to loop through all rows and the check if the value matches in that particular column.

Else we have excel funtions like VLookup or Match available in the excel to get the value easily

1 Like
3

Ok this is clear. I’m able to get through the rows and get the correct row based on the country provided.
But I’ve difficulties to make it working when i need to iterate all the columns too. Multiple “For each” activities is the correct approach?

I’ll check out the activities you mentioned “VLookup or Match” and try them.

1 Like
4

No, if you loop using for each row, and check as row(“country”).tostring = “value you want to check” then get the value of name as row(“role”).tostring

1 Like
5

This is what i have so far.
I’m going trough Rolelist (4 role listed)
Then I’m going through the datatable and get to the row i want based on the county.

Now i need to get go through the columns based on the role name(L_Role).
How can I achieve that ?

image
image588×734 20.1 KB

1 Like
6

Hi @abuzzi
Based on the new column you can match the record and assign it to the column
Use add data column
use add data row
and print the values by using outputdatatable

Thanks
Ashwin S

7

@abuzzi

First read your Excel Sheet and store the data in datatable dta.
Let us take your roles as ListA and User Country as Stra

Now use below query to extract data.

dtOutput = (From p In dta.Select 
           Where Convert.ToString(p("Country")).ToUpper.Equals(stra.ToUpper) and 
           ListA.Any(Function(x) Convert.ToString(p("role name")).ToUpper.Equals(x.ToUpper))
           Select p).ToArray.CopyToDataTable.

Regards,
Mahesh

1 Like