How to carry out a One to N (1 to N) 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 a One to N (1 to N) 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 value in both Excel sheets.
We will now try to compare a value from one excel sheet to each value of the other Excel sheet for all the available rows.
How to carry out a "One to N (1 to N)" comparison Flow within Leapwork?
A sample test case for a One to N (1 to N) 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 a One to N (1 to N) comparison Flow within Leapwork:
Expanded view of a One to N (1 to N) flow is shown below:
In the above image, we are comparing the FirstName value from the first row of the First Excel sheet to the FirstName value of each 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.
A Pass block is added at the completed section of the Second Read Excel Block since we want to loop to start from the second block.
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 a method as Row index so that it will execute for a particular row of the sheet and it can be changed as per requirement.
- The Second Read Excel Block has the method as Iterate so that it will execute for each row of the sheet. (see below image).
- The Compare Block is used to compare both the values fetched by the Read Excel building blocks.
Example of a successfully run flow showing the One to N (1 to N) 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 1 to N comparison.zip file.
- Import the attached use case, 1 to N comparison.zip , in the Leapwork Studio.
- Open the use case with the title, "1 to N 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 the 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.
Please refer to the following article for more : Organizing Daily Work in Leapwork.
Attachments: