I have an Excel
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
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:
Output:
Sequence.xaml (20.5 KB)
Cheers!!
how it will run in loop if there are more number of rows ??
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 -
Hope it helps!!
instead of Updating status in Col D what if that column is K or L ? will it work?
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 -
Output -
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,
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
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”
I hope this will you to understand it
Cheers
Happy Automation
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