Lookup data table not configured correctly

Help Activities
21

image

22

Don’t have that option:
image

23

@E.T.S

  1. First please note you will get -1 when there is no row found…
  2. So you need to verify if the data you are searching is exactly matching or are there any differences between teh strings may be in the form of spaces as well
  3. If you click on the settings icon you will see the icon on community site

Hope this helps

cheers

24

I have checked the strings and they are the same

Please could you show me where the settings icon is

Thanks

25

It’s fine, just copy/paste what you see from the debug panel for the datatable variable.

26

@E.T.S

  1. Can you please keep a break point on look up and verify the locals panel and check the variable values at the breakpoint by stepping in and see what value si there in the string being searched and the value actually in your datatable
  2. Settings is below

image

cheers

27

--------------------- removed

28

----------- ----------removed

29

This is the value in the first row for “Key Areas”

What value is in strKeyArea that you’re searching for with the lookup? It would have to be exactly the above text include the carriage returns. Again, lookup looks for an EXACT MATCH, not a contains or anything else. Exact match only.

30

strKeyArea:

@"Directors’ remuneration:

Engaged via a Personal Service Company (PSC)"

31

@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
32

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

1 Like
33

By carriage return do you mean new line

1 Like
34

I did - it looked the same to me

1 Like
35

@E.T.S

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

cheers

1 Like
36

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
37

How to I configure to remove exact match please

1 Like
38

@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
40

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
41

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