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 File 2.xlsx File 1.xlsx

Hi @Carolyn , Here is one Way: * Use a Multi-Row Formula to get the previous ID on the row. * Use Generate rows to create the new ID to Join on. * Join ID to new ID (discard R)

Join 2 files using ID numbers without sequential ID numbers

ccanterman

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

Color coded info.png

File 2.xlsx

File 1.xlsx

Datasets

Join

Data Investigation

Accepted answers

Clifford_Coon

Hi @Carolyn ,

Here is one Way:

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

File_1_2.yxzp

All comments

Clifford_Coon

Hi @Carolyn ,

Here is one Way:

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

File_1_2.yxzp

ccanterman

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.

dynamic replace.yxmd

ccanterman

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!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703