Lookup data table not configured correctly

@E.T.S

looks like the new line characters might be different in both…

did you try printing or logging both the values and check what is the diifference

cheers

1 Like

The value in your datatable has another carriage return after (PSC) but your variable doesn’t. They must match EXACTLY.

1 Like

By carriage return do you mean new line

1 Like

I did - it looked the same to me

1 Like

@E.T.S

As @postwick pointed check the last extra new line…also bettern try with removing exact match…

cheers

1 Like

It’s not the same.

Directors’ remuneration:

Engaged via a Personal Service Company (PSC)
,"

There is a carriage return after (PSC) which is why the comma is on the next line instead of right after (PSC).

1 Like

How to I configure to remove exact match please

1 Like

@E.T.S

My bad…the option is available for vlookup in excel activities but not the look up tables…

we can simlate this using filter datatable and if codntion…try as below

use fitler datatable with column as 0 and use contains and value as the string you are using

then use a if conditon to check if row si returned dt.RowCount >0

if yes then use dt.Rows(0)("ColumnName You need").ToString will give the required values

or linq can be used in assign will give the matched string or empty

dt.AsEnumerable.Where(function(x) x(0).ToString.Contains(YourStringVariable)).Select(function(x) x("ColumnYou need").ToString).FirstOrDefault

cheers

1 Like

Ah after debugging I pinpointed which activity is causing the removal of that extra line

I have an assign for the strKeyArea variable: Split(strRow, “*”) (1)

Is there a way i can edit this to not remove the space?

1 Like

The linq statement gave me a null value - is there a way to configure it where it is not an exact match i.e. it is a contains in the statement

1 Like

I think it may be a good idea to review your overall process because that data is very poorly formatted and the way you’re trying to work with it is wrought with potential problems.

What is the source of the data in your datatable? What is the source of the value in strKeyAreas? What is your overall process and goal?

2 Likes

The outcome was null for this

1 Like

The source of the datatable is an Excel file

The source of the strKeyAreas is another Excel file

The process is to navigate to a specific Sheet in Excel (this part the bot has done correctly), then find the row that matches the strKeyAreas variables and copy that row

I have tried assigns with the above suggestions from @Anil_G but they haven’t worked so far

I think the best way to do it is under my read range activity is to use an assign where the variable matches the cell in the row → unsure how to do this

1 Like

@E.T.S

Try this

dt.AsEnumerable.Where(function(x) Regex.Replace(x(0).ToString,"(\r?\n)*","").ToLower.Contains(Regex.Replace(YourStringVariable,"(\r?\n)*","").ToLower)).Select(function(x) x("ColumnYou need").ToString).FirstOrDefault

cheers

This works but doesn’t read the full row - i.e. it finds the match but reads one line

1 Like

@E.T.S

You need full row or a specific column values?

if you need full row remove the select function

cheers

The row of where there is a match please - not one column where there is a match

I removed the select function and got an error:
dtCorrespondingTab.AsEnumerable.Where(function(x) Regex.Replace(x(0).ToString,“(\r?\n)“,”“).Contains(Regex.Replace(strKeyArea,”(\r?\n)”,“”))).ToString).FirstOrDefault

1 Like

@E.T.S

If you need the whole row use thsi

dt.AsEnumerable.Where(function(x) Regex.Replace(x(0).ToString,"(\r?\n)*","").ToLower.Contains(Regex.Replace(YourStringVariable,"(\r?\n)*","").ToLower)).FirstOrDefault

And if you need it as datatable and all the matched rows if there are multiple…then bettern first check if any thing is matching using if and then use copyto datatable

To check matched dt.AsEnumerable.Where(function(x) Regex.Replace(x(0).ToString,"(\r?\n)*","").ToLower.Contains(Regex.Replace(YourStringVariable,"(\r?\n)*","").ToLower)).Count>0 use this in if…

And on then side use assign with dt.AsEnumerable.Where(function(x) Regex.Replace(x(0).ToString,"(\r?\n)*","").ToLower.Contains(Regex.Replace(YourStringVariable,"(\r?\n)*","").ToLower)).CopyToDataTable

Hope this helps

Cheers

2 Likes

Fantastic thank you! I used the one where I need the whole row and it worked!

2 Likes

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