Excel Operation- validation

Help Activities
, ,
1

I have variable var1 which contains value in this format PCD-123456 and another variable var2 whose value in PCD-123456-17-10-2023 format .

If I provide var2.contains(var1) it will validate the correct value.
But if condition is var1.contains(var2) it goes to else condition.

But I need to validate the situation on the basis of var1 variable.
Pls provide me the correct formula

1 Like
2

Hi @Luffy ,

In that case, the Expression does return the correct decision I suppose as the var2 is not present in var1 but var1 is indeed present in var2.

Could you maybe explain a bit more as to what is wrong accordingy to you ?

3

Ideally this is how we can approach as var2 has more value than var1

But if you want to go from var1 then u can convert them as a array and then compare like this in if activity

arr1.Intersect(arr2).Count = arr1.length

Where arr1 is obtained with assign activity like this

arr1 = Split(var1.ToString, “-“)

This will give array of string
Same we can do with var2

Cheers @Luffy

4

var2 contains 15000 item and var1 contains only 450 item.

If I use var2.contains(var1) , this will check all 15000 value in var1 450 items, which is more time consuming process.

but if somehow I am able to validate the 450 items of var1 in 15000 item of var2 , it will time less time to execute.

5

@Luffy ,

We assumed the values are string type based and not an array.

For the array type checks on contains, maybe the below could help :

var1.All(Function(x)var2.Any(Function(y)y.Equals(x))

Here, var1 & var2 are assumed to be Array of String type

The Expression above returns true if all the values of var1 are present in var2 array.

If the suggested expression does not work, then we would ask you provide sample data on both array values.

6

both var1 and var2 are string type

7

@Palaniyappan both variables are string type

8

@Luffy ,

As suggested to avoid further confusions, could you provide us with the Data Samples - Inputs and Expected Output

9

Still you can do this with the below method

@Luffy

10

@supermanPunch data I can’t provide but I can give you the high level understanding.

I am fetching these values from an API- PCD-123456-17-10-2023 ( values format will remain same and type is string

PCD-123456 and these values I am fetching from excel file column ( format will be same and type is string).

11

Hey @Luffy

if ‘-’ is only Seperator separating the group of chars in the var1 & var2 then, you can look into the below code.

var1.Split("-“c).ToList().All(function(x) var2.Split(”-"c).ToList.Contains(x))