Lookup Data Table but values contains '

Help Studio
1

hi all,

i have a value i am trying to find in a datatable, the value may or may not be there.

so the first thing i do is create a boolean variable (DatePresent) to check see if the value is present

image

DT_Values.Select(“Column1 like ‘Date de VL et d’actif géré’”).Length>0

then if the value is present i write the value to the dt, if not i write N\A to the dt

the issue i have is when the code runs it crashes the check for the date present because -

image

it does not like the ’ in the middle of the text i want it check

image
image917×172 4.12 KB

is there anyway around this please?

thanks,

2

Hi @adrian_sullivan

Try this

valuePresent  = DT_Values.AsEnumerable().Any(Function(row) row.Field(Of String)("Column1") = "Date de VL et d''actif géré")

Regards,

3

that is getting further - there is no error being thrown back.

however it is not picking up the date. let me throw a sample file together and post it up

4

so here is a short workflow

the final log message should have a date in it

Workflow.xaml (15.6 KB)

Test File.xlsx (10.7 KB)

5

Assign Activity
strValue =

(From d in DT_Values.AsEnumerable
Let cs = d(0).toString.Trim.ToUpper
Where cs.Equals("Date de VL et d'actif géré".ToUpper)
Select v = d(1).toString).DefaultIfEmpty("-1").First()

when found will return the value as string
when not found it will return -1 as string and you can adapt the default values as well

6

instead of using count and pay the processing costs of all rows evaluation we would use the Any LINQ Operator

7

@adrian_sullivan

image
image828×2535 201 KB

image

Sequence5.xaml (16.2 KB)

Regards,

8

But comming back to your origin problem root cause:

Reading DataTable without ticking the header will result to:
grafik

We see that Column0 is of interest and also the corresponding Column1 Value is interpreted as DateTime

grafik
it is failling as when you will haver a closer look we do get the hint to 'activ

with the single dot we confuse the syntax and we have to escape it with an additional one
grafik

9

It’s not supposed to pick up the date. The .Any operator returns true or false (boolean). Use it as the condition of your If.

Date de VL et d’actif géré

Is this supposed to be a variable? You have it as a literal. Your expression is looking for the literal text “Date de VL et d’actif géré” in Column1

10

thanks @postwick

It’s not supposed to pick up the date. The .Any operator returns true or false (boolean). Use it as the condition of your If.

what i meant is to say was that the job was running without any errors being created after i updated the code, so the boolean value was at least passing

what was happening though was that even though it looked as if the criteria should have been met, the job was still saying that the crieria was not met. ie it could not find a field with Date de VL et d’actif géré’” wvwnthough it was there

hence getting futher (job not crashing)
but the booelan values was being picked up incorrectly

i will have a look at this again in the morning, trying to write some of these statements can be an uphill challenge for me and then add in the fact that is in French just makes it even more “fun”

i apprecaite everyones help with it

adrian

11

@adrian_sullivan

lets summarize the different inputs from above:

  • we had shown you the reason and the fix

For:

we had introduce the Any Operator which is returning true or false depending on the presence:

For the depending Column2 Value lookup we also shared a few options e.g.

Also feel free to combine the different Approaches as we also have done (Select, Any, Value retrieval …)

So we do feel that the topic can be closed