Reducing execution time of two table data massaging from 5 days to 1 day?

Help Studio
, ,
1

So there is this two file SAP and DB(DarwinBox) i want to compare some data in both of the table and then write a new excel file

(this project is of my friend)

In the SAP file we have to check the date and thne check if the day in the DB file contains anything or not if it contains something then there are predefined rules to do some changes

well i guess you will understand it better if you just look bellow

Here is the exact rules

PDD2Ajay
PDD2Ajay1600×1197 211 KB

PDD1AJAY
PDD1AJAY1600×1197 197 KB

one of the image is the output file

here are the two input file and one output file

SAP_Copy.xlsx (1.9 MB)
Output.xlsx (125.7 KB)
sqs-reports_INSTANCE4_5f59a70756c57_89_Attendance-Summary-01-Aug-2022-to-20-Oct-2022_a635785ebdb06b.xlsx (12.6 KB)

The sqs-report is the DB file , the SAP_Copy is the SAP file and the Output.xlsx is the output file

The DB file currently have just 3 rows and still the out is getting apprx 5k rows imagine if the DB file in the orginal data with 35k rows and 90 cloumn

and now the workflow (enterie workflow is ready just need to reduce the time from 5 days to 1 day execution)
Attendance Flow.zip (6.2 MB)

2

Hi @indiedev91 ,

It seems that we could maybe reduce the inner For Each by using a Filter at First to Just fetch the rows which match the dates. Further we can perform a Join Operation between the two Datatables based on the Employee ID. We should then be able to perform the operation by just Looping through one Datatable (First For Each Row).

However, Could you provide all the different columns that are being compared between the two datatables ? We could maybe provide a corrected workflow file for your reference based on this.

3

1:- In the SAP file the column name "PERNR which is the “Employee ID” is being compared to the column “Employee ID” in the DB file (the sqs report file or the Darwin Box as mentioned above, I will just call it DB for simplicity)

2:- in the SAP the column name “SUBTY” also called as the SAP code is being compared to current row of the dates column which holds the “P” and “A,U,SA” and print according to the given condition above in the image

3:- in the SAP the column name “ENDDA” or the Date is being compared for each employee with the column names “01 Mo Aug … so on which holds the dates” in the DB

i hope i explained it properly but if you have any doubts regarding it please ask me

4

Ok, I have an idea, just do not know how to implement it here. I have heard parallel foreach makes the execution time faster, but I haven’t used it so I don’t know how to implement it here and if it will even be helpful for my particular scenario.