Matching data from one collection to another and marking flag in collection

[SwatiAgrawal]

Hi,

I have an output collection with certain rows and values defined like this in the "current values"-

Now, I have a separate collection with Account Numbers only.


Please note: the series and count of account  numbers in second collection may not be same as first collection. However, there may be a possibility that accounts in second collection are same for few of the accounts of first collection.
Now my requirement is that - 
1. I want to add one more column in collection 1 with column name - "Doc Present" & Type: "Flag"
2. Now, I want to check if each account in collection 2 is present in collection 1 or not? and then mark as "True" or "False" in "Doc Present" column against that particular account in collection 1 basis that account was found or not.

Looking forward to your help...
Thanks in advance !!


------------------------------
Swati Agrawal
------------------------------

[michaeloneil]

Hi @Swati Agrawal

If you need to add a column to your collection while the process is running you will need to create a new action with a new code stage to do this. I'​ve added the code below for this, as for searching through one collection and comparing with another you could try doing a nested loop and compare each row and if they match then mark it as a match. See the example below, hope this helps 🙂

Insert column in collection code: inputs - Input Collection, Column Name, Column Index (Where the column should be added e.g. 2)
Outputs - Output Collection
----------------------------------------------------
Output_Collection = Input_Collection.Copy

Dim Newcol As DataColumn = New DataColumn(Column_Name, GetType(String))
Output_Collection.Columns.Add(Newcol)
Output_Collection.Columns(Newcol.ColumnName.ToString).SetOrdinal(Column_Index)
--------------------------------------------------------------------------------------------



------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[Devendra_KumarP]

Hi Swati, 

You can use Filter collection. it will be quick.
Filter collection Expression :  "[Account No] = '" & [Accounts Data.Account Number] & "'"

Let me know if you need any info related to filter collection.

------------------------------
Devendra Kumar Prajapati
Tech Lead
Infosys
Europe/London
------------------------------

[AmolJangam]

Use the Filter Collection as suggested by Devendra, It will save lot of time in process if collection rows are in thousands.
Alternatively, If collection data is present in excel documents then you can use OLE DB Utility for this scenario.

------------------------------
Amol Jangam
test lead
infosys
Asia/Dili
------------------------------