How to carry out an N to One (N to 1) comparison within Leapwork?
Overview:
While working with Data, users often reach a point where they'll have to compare the data coming from one data source (Excel, CSV, Database, etc.) with another. In the article, we'll be discussing how we can carry out an N to One (N to 1) comparison in Leapwork using the Read Excel building blocks.
In this scenario, we will have two excel files containing some random names in a column. We have provided 9 rows of values each in both excel sheets.
We will now try to compare each value for all the available rows from the first excel sheet to one value of another excel sheet.
How to carry out an N to One (N to 1) comparison Flow within Leapwork?
A sample test case for an N to One (N to 1) comparison between two excel sheets is shown below by creating a custom building block (also called sub-flows in Leapwork) so that it can be reused later on.
We will start building our flow with a Start Block. We would then click to add a Read Excel Block inside the sub-flow and use a Compare Block to carry out a comparison of the two values.
Typical view of an N to One (N to 1) Comparison Flow within Leapwork:
In the above image, we are comparing the FirstName value from each row of the First Excel sheet to the FirstName value in the first row of the Second Excel Sheet.
It is worth mentioning that since there is no Fail Block added in the flow, hence the iteration will read all the selected values (as per the defined range) from the Excel sheet.
The Pass block is added at the completed section of the first Read Excel Block since we want to loop to start from the first block itself.
However, a user can always add a Fail Block by connecting it to the Incorrect section of the Compare Block so that iteration can stop if the values don't match.
Please see the below image for a failed scenario:
Under the hood:
- The First Read Excel Block has the method set as Iterate so that it will execute for each row of the first sheet (see below image).
- The Second Read Excel Block has the method set as Row index so that it will execute for a particular row of the sheet. Row index can be changed as per the requirement.
- Compare block is used to compare both the values fetched by the Read Excel building blocks
Example of a successfully run flow showing the N to One (N to 1) comparison of excel sheets with the respective Studio Logs:
The highlighted area in the above image shows the comparison of the value from the same rows of the first and second Excel files.
Note:- We can always use a .csv file to perform the same task as above.
Quick Start:
- Download the attached N to 1 comparison.zip file.
- Import the attached use case, N to 1 comparison.zip , in the Leapwork Studio.
- Open the use case with the title, N to 1 comparison.
- We can always wrap the 2 Read Excel blocks to create a sub-flow as shown above in the typical view screenshot but here we have used individual blocks instead.
Configuration and Use:
After the flow has been imported, you can move the flow to any specific folder and then execute the same to see the behavior.
Support:
For any clarification, please contact our Priority Support.