Join 2 files using ID numbers without sequential ID numbers

[Carolyn]

Hi! I'm hoping someone can help me...

I have 2 files: 

• File 1 = Daily credit card transactions where they're summed by daily ID numbers into batches.
  • For example, ID 170 in the screenshot below indicates that I had $5 in credit card transactions as part of the batch ID on that day, which was 170. 
• File 2 = Aggregate that was paid by the credit card merchant. They don't pay on a daily basis. When they do pay, they pay everything for that ID and lower that hadn't been paid yet. It's also net of fees
  • For example, when ID 174 was paid, its total was for 174 and lower, which = 170, 171, and 172 in this case
  • ID 178 was paid and it covered 178, 177, and 176.  

In the screenshot below, I color coded to show which lines go together. I'm also attaching the Excel sheets. 

I'm stuck on how to join (or otherwise connect) the 2 files. The only thing I can think of is having a Formula Tool do "ID + 1", "ID + 2", "ID + 3" for awhile for File 1 and then do a bunch of Join Tools so if File 1 ID = File 2 ID -> done, otherwise, check if File 1 ID + 1 = File 2 ID, otherwise..., and just keep doing that, but that seems silly

[Clifford_Coon · Accepted solution]

Hi @Carolyn ,

Here is one Way:

1. Use a Multi-Row Formula to get the previous ID on the row.
2. Use Generate rows to create the new ID to Join on.
3. Join ID to new ID (discard R)

 

 

[Carolyn]

Hi @Clifford_Coon , 

That's exactly what I needed - thank you!

[SPetrie]

If I am understanding correctly, you want to tag the rows in file 1 with the id from file 2 that it was paid with?

Im not sure where the totals are coming from since file 1 is round numbers and file 2 has decimals, but I think I can help with the tagging part.

You can use a dynamic replace to add formulas to check the values.

The basic gist is that I use File2 to build if statements that evaluate to True or False. It evaluates them in order, so what comes out needs to be sorted.

It then applies those to a tag field I created and adds the ID to the Tag field if it fits the criteria.

I randomized the order of File 1 just to make sure that File 1 order didnt matter (which it doesnt, but File 2 order does)

 

 

 

example workflow is attached.

 

[Carolyn]

Hi @SPetrie, 

Thank you for that workflow with the Dynamic Replace. I haven't used that tool yet but that's exactly what I'm trying to do (I'll ultimately want to compare totals from File 1 to File 2 to calculate the fees, but that's easy once they're together. 

Thank you!