Match DT data with other DT and get the email address

[Sathish_Kumar_S]

Above screen captured using Table Extraction activity and stored in to DT

We need to match name value stored in above DT with below Mapping Table DT and get the Email Address.

For Example : Erika Majszin available in above website and we need to match with below mapping table and get the email address stored in below table. Please suggest

[postwick (Paul Ostwick)]

This is a standard database operation called a join. Use the Join Data Table activity.

[slopezchavez (Samuel Lopez)]

A quick solution would be:

1- Create a for each that loops the number of columns that your first datatable has. (for each: Enumerable.Range(0,YourFirstDatatable.columns.count).ToArray()

2- Use the index of the loop to get the names: CurrentName = row(0)(index)

3- Within the loop use a select to find the name in the second datatable: MatchedRows = YourSecondDatatable.Select(“Name= '”+CurrentName+“‘’”)

4- Get the email for the current name: emailAddress= MatchedRows(0)(“Email address”)

Hope this helps.

[Palaniyappan (Palaniyappan P)]

Hi

We can do this simply with a lookup datatable activity

Have a view on this doc for more detail

Activities - Lookup Data Table

Or if u would like to go expressions then this thread looks suitable for ur scenario

How to lookup two data tables?

Hi @aishwarya1 u can try this assume dt1 —> sheet1 dt2 -----> sheet2 dt3_uncommon= (From r1 in dt2.AsEnumerable Where dt1.AsEnumerable().Where(Function(row) row(“Organization Name”).ToString.Trim.Equals(r1(“Organization Name”).ToString)).Count=0 Select r1).CopyToDataTable Now use append range or merge datatable to merge the dt1 and dt3_uncommon to a excel sheet. Regards, Nived N

Cheers @Sathish_Kumar_S

[postwick (Paul Ostwick)]

You can do this with a single Join Data Table activity.

[Sathish_Kumar_S]

@slopezchavez

2- Use the index of the loop to get the names: CurrentName = row(0)(index)

2- Use the index of the loop to get the names: CurrentName = row(0)(index) - But there is NO fixed index number for Name column in FIRST Data table … how to handle this?

[Sathish_Kumar_S]

@slopezchavez

Enumerable.Range(0,YourFirstDatatable.columns.count).ToArray()

Getting this error

[slopezchavez (Samuel Lopez)]

It’s because you need to use a ‘for each’ not a ‘for each row’

[Sathish_Kumar_S]

@slopezchavez

2- Use the index of the loop to get the names: CurrentName = row(0)(index)

3- Within the loop use a select to find the name in the second datatable: MatchedRows = YourSecondDatatable.Select(“Name= '”+CurrentName+“‘’”)

4- Get the email for the current name: emailAddress= MatchedRows(0)(“Email address”)

Yes now i have changed to For each activity now

2- Use the index of the loop to get the names: CurrentName = row(0)(index) - What will be variable type for this?

And the i can’t use always same index number as the name value will be in any index in First DT

Tried 2 index umber for testing and getting below error

[Shiva_Nikhil]

@Sathish_Kumar_S

Make the below datatable2 to dictionary

Dict_values=Dt.Asenumnerable.todictionary(Function(x) x(0).tostring,function(r)r(1).ToString)

Use for each row in datatable for datatable 1

Inside use

Assign

strEmailaddress=dict_values(currentrow(“App3”).tostring)

StrEmailaddress is the variable and you will get the output as
Erika@gmail.com

Cheers

[Sathish_Kumar_S]

@Shiva_Nikhil

strEmailaddress=dict_values(currentrow(“App3”).tostring)

Thank you for you response @Shiva_Nikhil
strEmailaddress=dict_values(currentrow(“App3”).tostring) → But name will not be always in App3 column… it will keep change

[Shiva_Nikhil]

@Sathish_Kumar_S

No problem

Just replace the columnname at the app3

You will get the same results beacuse the values in the dictionary as constant

[Sathish_Kumar_S]

But we wouldn’t the exact column name

[Shiva_Nikhil]

@Sathish_Kumar_S

If you know the name

Then take one assign and directly pass that name no need for each row in datatable activity

StrEmailaddress=dict_values(“Erika Majsan”).tostring

If you want to find is there are not then use below gives boolean output

Dt.rows(0).itemarray.any(function(x) x.tostring.contains(“Erika Majsan”))

[Palaniyappan (Palaniyappan P)]

Fine

Hope the below steps would help you resolve this

1. Let’s take the first datatable is dt_1
2. Second one is named as dt_2
3. use a assign activity like this

arr_input = dt_1.Rows(0).ItemArray

This will give the first row as array which will hold the name we want to search
arr_input is a variable of type array of string

4. Now use a FOR EACH ROW activity and pass the second datatable as input

Inside the loop use a IF condition like this
arr_input.Contains(CurrentRow(0).ToString)

If true then goes to THEN block where u can get the mail id like this with a assign activity

str_mailid = CurrentRow(1).ToString

Followed by this assign activity in THEN block use a BREAK activity to stop the loop

If the condition is false then it continues with ELSE Block and goes further

Cheers @Sathish_Kumar_S

[Sathish_Kumar_S]

We don’t know the name also … because each queue transaction will have different buyer names

[Palaniyappan (Palaniyappan P)]

That’s why with the above mentioned steps u don’t need to worry about the column position or even the Name value and position
It will just compare and get the value without using index position or even the column name

Check the last post and let us know the outcome

@Palaniyappan

arr_input = dt_1.Rows(0).ItemArray

@Sathish_Kumar_S

[Shiva_Nikhil]

@Sathish_Kumar_S

use for each row in datatable for second datatable

if activity

datatable1.rows(0).itemarray.any(function(x) x.tostring.contains(currentrow(0).tostring))

then

assign strEmailaddress=currentrow(1).tostring

else
no activity need in else part

cheers