Join Data table activity is not working

[Muhammad_Rameez_Imtiaz]

Hello,

I am trying to filter some data based on another table hence using join data table activity.
Table 1 consists of 16000+ records and table 2 contains only 30 records with Prescription Number from table 1 matches with RxNumber of table 2. I am using inner join to remove data that does not match but I am getting zero rows. I have manually verified and all 30 records from table 2 are present in table 1. Can anyone help?
@Palaniyappan

[sonaliaggarwal47 (Sonali Aggarwal)]

@Muhammad_Rameez_Imtiaz

Can you show the structure of both tables?

Also before join, can you verify if any data is there in the input data tables?

[postwick (Paul Ostwick)]

I assume you’re reading this data from Excel? Excel activities are a nightmare, they love creating datatable columns as datatype object. Here’s what you need to do. Do this for each datatable.

• Add Data Column - STR Prescription Number - datatype String
• For Each Row in Datatable
• -Assign CurrentRow(“STR Prescription Number”) = CurrentRow(“Prescription Number”).ToString
• Remove Data Column Prescription Number
• Assign yourDt.Columns(“STR Prescription Number”).ColumnName = “Prescription Number”

Do the same for the RxNumber column in your other dt. Basically you’re converting the column to string.

Now do the Join.

[Muhammad_Rameez_Imtiaz]

I am reading data from excel in both datatables. YEs i can confirm data table rows are not 0. Infact If i change join type to full I get 16000+30 rows.

[Muhammad_Rameez_Imtiaz]

It used to work fine. I just did a last test before deploying the bot and it didnt work. If u can share the code I will be grateful

[postwick (Paul Ostwick)]

I can’t share the code, I just wrote the pseudo-code off the top of my head. Create string columns in each dt. Then two simple For Each Row in Data Table loops. Then remove original columns and rename the new columns you added.

If it was working before and isn’t now, possibly something changed with the source files so now the Excel activities are creating the columns as Object instead of string, datetime, etc.

[sonaliaggarwal47 (Sonali Aggarwal)]

@Muhammad_Rameez_Imtiaz

That means it’s not able to equate.

Can you confirm both data are in the same format in excels? Are both columns a text format in excel? May be one is number? Or any other format?

[Anil_G (Anil Gorthi)]

@Muhammad_Rameez_Imtiaz

Welcome to the community

The data type mismatch can cause the issue

If you want to only filter the data from table2 for what is matching with table1 then you can use linq directly to handle the same..use assign as below

Dt2 = dt2.AsEnumerable.Where(function(x) dt1.AsEnumerable.Any(function(y) y("Prescription Number").ToString.Equals(x("RxNumber").ToString))).CopyToDataTable

This will ensure all the rows in dt2 which are matching with dt1 are present

Apart from this if you want to join itself then it is good to create a new column in tboth tables as string type using add data column

Then use assign with dt1.Columns("NewColumn").Expression = "[RxNumber]" repeat same for other table and column

Then join on this new column which are of string type..then it eould work as expected in join as well

Cheers