How to filter the data in the excel based on the columns

Help Studio
, ,
1

Hi I have 2 excel files one is input Excel file and the other is Output excel file.
Note:RecordID and WFID both are same

In Input and Output Excel file i have Record ID column which is common and in output Excel file i have one more column with PO Numbers

First i need to Check whether the Input Record ID present in Output file and Next PO Number Column Value for that record ID in the same output file is Empty

Then we need to consider that record if PO Number coulmn is having some value we need to discard.

Input file Sample

image
image799×132 5.87 KB

Sample Output File:
image

Thanks
Likitha

2

Read both input and output Excel files and store them in separate DataTable variables

then Use the Join Data Table activity to join dtInput and dtOutput on the “Record ID” column. Set the JoinType property to “Left” to keep only the records from the left table (dtInput) that have matching records in the right table (dtOutput). This will give you the records from the input file that are present in the output file.

After Joining Add a Filter Data Table activity to filter the joined result and keep only the records where the PO Number column in dtOutput is empty. This will ensure that you consider only those records where the PO Number is not already filled in the output file.

The resulting DataTable will contain the desired records. You can use this DataTable for further processing or write it to a new output Excel file if needed.

3

@vinjam_likitha

Use join datatable activity
Use condition “Record Id”=“WF ID” and “PO number” IsEmpty

4

hi @vinjam_likitha
dt2
then
dt3

5

Below is theJoin Datatable activity with its parameters
image

Below is the Filter Datatable activity with its parameters

image
image1008×418 21.9 KB

Hope this helps you get a clear picture

6

Hi ,

After filtering my input file should contain only the results record id(Filtered results)

I should not create new input file i need to only output of filtered records in my input file remaining should get discarded

Thanks
Likitha