find and replace between two collections

[jacobs35]

Hi All,
i'm 100% new to this so hopefully i'm clear enough.
i have 2 collections. 1 is 2 colums that came from excel (lookup) and one a data dump that i imported into a collection from a tab delimited text file (data)

what i want to do is loop over the data table and replace a value in a specific column when a corresponding match is found.

IE
(lookup)100, ABC
(data)text, text, text, 100, aaa
should become
(data)text, text, text, 100, ABC

the question: am i looking at a code solution or is there a delivered function i haven't found yet for this type of action?

 

[VJR]

Hi jacobs35,

I would like to understand more of your requirement in order to offer you a solution that you are looking for.
Can you explain as follows:

Collection Name: Lookup
Column 1 | Colulmn 2
100 | ABC

Collection Name: Data
Column 1 | Column 2 | Column 3 | Column 4
text | text | text | 100 | aaa

This may not be right since I just made it up for you to explain as above and what needs to be done in the collection and how should it look after.

 

[jacobs35]

hi VJ.
yes.
collection 1 - has 2 column. GL account and FSLI
collection 2 - has records from a GL dump including GL account and various other fields.

i want to read through collection 2 on column2 and where the GL accounts match; i want to replace the value in col5 with the FSLI value from collection 1.
i've provided screen shots of the collections below.

i'm thinking this should be simplistic and i'm just missing something obvious. hopefully clearer now!

 

[VJR]

Hi jacobs35,


Your process diagram would look something like this
View attachment 1529592023264.png

- Loop through Collection 2. Collection 2 is your collection having the 5 columns.
- Apply filter on Collection 1 for the value in Collection 2.
This returns a new collection called Collection Out.
- If the matching data was found after applying the filter then Collection Out will have more than 0 rows. From your post it looks like only 1 matching row will be returned always.
- Use the 'Count Rows' action of Collection VBO (not Collection Utility VBO) to count the rows.
- If the Rows are more than 0 than use a Calc stage to set the value of Collection Out of the desired column to the Collection 2 column which is already running in the loop.
- If no matching rows are found then nothing to do and get the next item in the loop, so you can direct the flow to the Loop end.
- Once the loop is complete the Collection 2 will have all the new values updated for that column.

Post back if you are facing any difficulty in implementing the above.

 

[jacobs35]

hi VJ thank you!
hadn't thought of the filter routine.
only issue i'm having now is related to the filter itself.
i'm getting the following.

Solved: i was using the filter wrong. i needed to point the columns to each other. EG [collection1.col2] = [collection2.col1]
i've tried keying the value directly or a dynamic reference.
Internal : Could not execute code stage because exception thrown by code stage: Filter expression '12000' does not evaluate to a Boolean term.

i did validate that the value '12000' is in the collection. i also confirmed it was in double quotes. additionally i've dynamically referenced it [collection1.col2] and get the same result

 

[VJR]

Hi jacobs35,

The format of filtering a collection for a Text data type is to enclose the value within single quotes

Format: "ColumnName = '" & [TextValue] & "'"

Example: "OnlyColumnName = '" & [CollectionName.ColumnName] & "'"

Based on your collection screenshots it will be-
"GL = '" & [Collection2.Col2] & "'"

 

[jacobs35]

thanks VJ. you're absolutly right, i still had the filter wrong. it works perfectly now.
With my previous attempt it was looping but replacing with the result of the first item found.

thanks again, i appreciate your patience and help!

 

[Mwacuka]

Hi VJR,

I have a almost similar requirement though with a slight twist.

I need to match data between two collections, one collection (Charges) contains a unique transaction number, employee ID and the total charges per employee. The other collection (Expenses) contains unique transaction number, employee ID and single expenses, this collection has more records than collection one (Charges).
My goal is to filter out and sum the expenses where the unique transaction number matches the Charges' unique transaction number and create a new collection which contains the employee ID, total charges per employee and total expenses per employee. I tried looping through the second collection (Expenses) to get the sum of expenses, but the values provided are incorrect.

Thank you for your time and looking forward to your response.

 

[VJR]

I may not be able to fully visualise what you are referring to. But probably you can make sense out of this-

Charges:
UniqTransno | Emp Id | TotalCharges
1
5
6


Expenses:
UniqTransno | EmpId | SingleExpenses
1 | emp1 | 10
2 | emp2 | 20
3 | emp3 | 30
1 | emp1 | 40
4 | emp4 | 50

1. Loop through the Expenses collection (if that is your Main collection)

2. Read the UniqTransno value. Check if this matches against the UniqTransno of Charges collection using 'Collection Contains Value' action.

2.a If present,
Filter this UniqTransno on the Expenses collection. This will result in another collection eg; FilteredCollection.
Loop through FilteredCollection and sum the running total of the SingleExpenses column.
Once the loop is over you will get the TotalExpenses and then insert a row for the desired columns (EmpId, TotalCharges, TotalExpenses) into a new collection eg; FinalCollection.

One important thing here. Before inserting a new row into the FinalCollection, check whether this EmpId (or UniqTransno as per your requirement) already exists in the FinalCollection using 'Collection contains value'. If it exists then it means that you already calculated the total for this and there is no need to add a new row. This will give the correct results in a situation like - if there is a UniqTransno of 1 in the first row of the Expenses collection as well as the second row from the bottom. So the total won't be made twice. That is the reason you may be getting incorrect sums.

In step 2/2.a you might also need to collect the TotalCharges from the Charges collection to insert it into the FinalCollection.

2.b If not present do nothing and continue with loop

3. At the end of the outer loop, the FinalCollection will have the desired data.

 

[Mwacuka]

I may not be able to fully visualise what you are referring to. But probably you can make sense out of this-

Charges:
UniqTransno | Emp Id | TotalCharges
1
5
6


Expenses:
UniqTransno | EmpId | SingleExpenses
1 | emp1 | 10
2 | emp2 | 20
3 | emp3 | 30
1 | emp1 | 40
4 | emp4 | 50

1. Loop through the Expenses collection (if that is your Main collection)

2. Read the UniqTransno value. Check if this matches against the UniqTransno of Charges collection using 'Collection Contains Value' action.

2.a If present,
Filter this UniqTransno on the Expenses collection. This will result in another collection eg; FilteredCollection.
Loop through FilteredCollection and sum the running total of the SingleExpenses column.
Once the loop is over you will get the TotalExpenses and then insert a row for the desired columns (EmpId, TotalCharges, TotalExpenses) into a new collection eg; FinalCollection.

One important thing here. Before inserting a new row into the FinalCollection, check whether this EmpId (or UniqTransno as per your requirement) already exists in the FinalCollection using 'Collection contains value'. If it exists then it means that you already calculated the total for this and there is no need to add a new row. This will give the correct results in a situation like - if there is a UniqTransno of 1 in the first row of the Expenses collection as well as the second row from the bottom. So the total won't be made twice. That is the reason you may be getting incorrect sums.

In step 2/2.a you might also need to collect the TotalCharges from the Charges collection to insert it into the FinalCollection.

2.b If not present do nothing and continue with loop

3. At the end of the outer loop, the FinalCollection will have the desired data.

This worked, thank you very much.