Comparing Values

I have an Excel
image
I want to check if peak value lies in between Min and Max column then Column D should be filled as OK if not then HIGH
in this scenario D2 D3 should come as OK and D4 as HIGH
what will be the logic

Hi @Pogboom

Please check the below workflow


Main.xaml (15.0 KB)

Input:

output:

Regards

Hi @Pogboom

Can you try the following?

Code:

(From row In DT.AsEnumerable()
                   Let min = row.Field(Of Double)("MIN")
                   Let max = row.Field(Of Double)("MAX")
                   Let peak = row.Field(Of Double)("PEAK")
                   Let status = If(peak >= min AndAlso peak <= max, "OK", "HIGH")
                   Select DT.Clone().Rows.Add(min, max, peak, status)).CopyToDataTable()

Input:

image

Output:

image

Sequence.xaml (20.5 KB)

Cheers!!

how it will run in loop if there are more number of rows ??

@Pogboom

Any number of rows the above code will work

Hi @Pogboom

→ You can use the Read range workbook activity to read the excel and store in a datatable datatype variable, let’s call it as dt_Input.
→ After read range use the For each row in datatable activity to iterate the each row in the datatable.
→ Take an assign activity to check the condition and update the STATUS Column.

- Assign -> CurrentRow("STATUS") = If(Cdec(CurrentRow("PEAK"))>=Cdec(CurrentRow("MIN")) and Cdec(CurrentRow("PEAK"))<=Cdec(CurrentRow("MAX")),"OK","HIGH")

→ Use the Write range workbook activity outside the for each to write the datatable to the same sheet.

Check the below image for better understanding,

Workflow -
2024.xaml (21.7 KB)

Output -
image

Hope it helps!!

instead of Updating status in Col D what if that column is K or L ? will it work?

@Pogboom

After writing the data, you can write the code to write into your required column

It will work for my code you can check the above post

It will search for the Column name and update there, no matter where the column is in K or L.

Input -
image

Output -
image

Hope you understand!! @Pogboom

Hi @Pogboom

Please check the below workflow and it works for your scenario where ever the status column will be

WorkFlow:


Main.xaml (16.2 KB)

Input:

output:

Regards

why did you use count = 2 any specific reason? and also below you used count = count +1 didnt get that part

Hi @Pogboom

Please avoid that. I’m sorry for that confusion.

You can delete that it’s not necessary.

Regards

Hi @Pogboom

Please check this ,I have updated the code.


Main.xaml (15.0 KB)

Regards

Hi Pogboom,
Step :1 Read the Datatable and Assign variable as “dt”
Step:2 Take For each Row in Data Table activity pass “dt” and take output as idx variable


image


Step:3
Assign
MIN=Convert.ToInt32(CurrentRow(0).ToString) for MIN variable variable aType as Integer
MAX =Convert.ToInt32(CurrentRow(1).ToString) for this variable Type system.Decimal since the data is Decimal
MAX =Convert.ToInt32(CurrentRow(2).ToString)

Assign If Condition and write syntax as “Peak>Min and Peak<=Max”
In then condition assign variable currentRow(“STATUS”) = “OK”
In else condition assign variable CurrentRow(“STATUS”) = “HIGH”

Step :4 take Write range and Pass the “dt”

image

I hope this will you to understand it

Cheers
Happy Automation

Hi @Pogboom

Try this:
Test.zip (3.2 KB)

Happy Automation :slight_smile:
Cheers!!

Hi @Pogboom

You can use column expression for this without need to loop the data table

in assign activity:

dt_SampleData.Columns("STATUS").Expression = "IIF(PEAK >= MIN AND PEAK <= MAX , 'OK', 'HIGH')"

Here the docs about it

Cheers

have an overview of the the different options

1 Like