Join data and filter

Help Activities
, ,
1

I have 2 file (input , report)
input1.xlsx (9.2 KB)
report.xlsx (9.0 KB)

image

Now I have filter file input that column Check_Duplicate = “N”

But If I want to check more with file report.

1.Map column SR from file input with column SR1 file report
1.1 Check match column act from file input with column act1 file report
- found match check column close in file report that have value not “working” or not blank → copy this row from input file to datatable.
1.2 Check can’t match column act from file input with column act1 file report
- found match check column close in file report that have value not “working” or not blank → copy this row from input file to datatable.

  1. Can’t find column SR in file report column SR!
    2.1 copy this row from input file to datatable

Example output : highlight row green I want to copy to datatable
image

Please guide me for solve it.
Thank you.

2

Can someone help me for solve it?

Thank you.

3

@Stef_99 Just prepare and send the expected output of dummy data!

Regards,
Ajay Mishra

1 Like
4

@Ajay_Mishra file input have sheet input and expect data as attached.

input1.xlsx (10.9 KB)
report.xlsx (9.0 KB)

Please guide me for solve it.
Thank you.

5

@Ajay_Mishra If you have question about this.

You can ask me ?

Thank you

6

Specify where we have store these data?(Structure of Datatable)

Regards,
Ajay Mishra

7

@Ajay_Mishra

new dataTable

8

Column names or do you have any kind of dummy table?

Regards,
Ajay Mishra

1 Like
9

@Ajay_Mishra You mean column name in expect output right?

If yes, all column in file input.

10

@Ajay_Mishra Can use LINQ for solve it?

Please guide me for solve it.

11

@Stef_99 Ya Sure! I have started working on it!

1 Like
12

Hey @Stef_99

Got the Solution!

For Building Output Datatable:

dt_Output = dt_Input.Clone

image
image1215×227 10.4 KB

For Filtering Check Duplicate with “N”

dt_Input = dt_Input.AsEnumerable().Where(Function(x) x("Check_Duplicate").ToString.Trim.ToLower.Equals("n")).CopyToDataTable

image
image1119×192 9.26 KB

If No SR Number Not found:

dt_Output = dt_Input.AsEnumerable().ExceptBy(dt_Report.AsEnumerable().Select(Function(r) r("sr1").ToString),Function(r) r("SR").ToString).CopyToDataTable

image
image1311×353 20.2 KB

For Main Logic:

dt_Input = (
From inputrow In dt_Input
Join reportrow In dt_Report
On inputrow("SR").ToString Equals reportrow("sr1").ToString And inputrow("act").ToString Equals reportrow("act1").ToString
Where Not reportrow("Close").ToString.ToLower.Trim.Contains("working")
Select dt_Output.Rows.Add(inputrow.ItemArray)
	).CopyToDatatable

image
image1366×441 23.5 KB

Note: I have surrounded LinQ with Try catch Actvity for Exception Handling. For scenario like No data row generated!(No Output)

Attaching .xaml for your reference!
JoinDataFilter.xaml (12.9 KB)

image
image974×1463 46.8 KB

Just execute the .xaml
You will get the expected Output!

Regards,
Ajay Mishra
RPA Developer

1 Like
13

@Ajay_Mishra I will try and update to you.

14

@Ajay_Mishra I have a question

If output write dt_output only.

Why you check in try catch main logic variable dt_input
But not use dt_input in data that write in excel output.

15

@Stef_99 Good Question!

As you can see in Main Logic LinQ we are adding Rows in dt_Output datatable. So the final output will be stored in dt_Output!

But if there will be no rows after filteration of Main logic then assign activity will throw an error and to handle that I have surrounded it with Try Catch block! and at the end no output will be generated in this situation.

If you got expected output, just mark the above post as solution!
It will help others to find the correct solution.

Regards,
Ajay Mishra
RPA Developer

1 Like
16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.