cancel
Showing results for 
Search instead for 
Did you mean: 

How to count Data

Sai
Level 2
Hi Team,
I have an employee excel file ,there i have to find out how many days an employee "Present & Absent'.
Note; p=present,  A=Absent. for reference please find below data.

17055.png

------------------------------
Sai
------------------------------
1 REPLY 1

Hi Sai,

For your requirement, I created a workflow as below:

17039.png

My input excel file looks something like this:

17040.png

In the first half of the workflow, I am reading the data from excel using basic excel actions such as Create Instance, Open Workbook, Get Worksheet As Collection and Close Instance to get desired dataset into a collection called as Data:

17041.png



Once, I have my Data Collection ready, I am iterating through my data collection using a Loop stage first. Within the loop stage, I use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Filtered Name Collection'. This collection will consist of the record from the Data collection where Name column is equal to the current iteration Name column value. Below are the filter parameters:

17042.png
'Filter Name Collection' current value after executing the action will be:

17043.png

Now, I use the 'Transpose Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Transposed Collection' by providing the input collection as the 'Filtered Name Collection' which I generated in my prior action . The action parameters are as below:

17044.png'Transposed Collection' current value after executing the action will be:

17045.png

Now, I again use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Absentees Collection'. This collection will consist of the record from the 'Transposed Collection' generated from the prior action where Value column is equal to 'A' . Below are the filter parameters:

17046.png
'Absentees Collection' current value after executing the action will be:

17047.png
Next, I again use the 'Filter Collection' action from 'Utility - Collection Manipulation' business object to get a collection named 'Presentees Collection'. This collection will consist of the record from the 'Transposed Collection' generated from the prior action where Value column is equal to 'P' . Below are the filter parameters:

17048.png
'Presentees Collection' current value after executing the action will be:

17049.png

Now, since I have two collections with me which provide me rows with presentees records and absentees records, so I can simply get the count of rows for each of these collections into separate data items:

17050.png

At the end, I add rows to a Defined collection called as 'Final Collection' with three fields namely, Name, Number Of Absent Days and Number Of Present Days:

17051.png
17052.png

At the end of the workflow execution I get the Final Collection as follows:

17053.png



------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please provide a big thumbs up so that the others members in the community having similar problem statement can track the answer easily in future.

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Technical Business Analyst,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------