Issues Joining Transaction Details to Primary Dataset

hunter_brown

I have two datasets. One is structured as the "primary" expense file, where each row is dates and other dimensions for each credit/debit. The next is credit card transaction details.

They share transaction IDs in a column named Doc, share GL #'s, and share posted dates. The combination of all 3 of these things will result in a unique join. My issue is this: there are other values besides transaction IDs in the Doc column in the expense file. Below is what I mean:

The orange underline are the other expenses in the source file that are not credit card transactions. But the transaction IDs are in that same column. This is my issue. How can I properly join on transaction ID so that the output of the ETL below is the entire expense file but with CC transaction details?

I tried seperating the process of the join and then appending to the expense file but that hasn't worked properly. Below is my current ETL, I am happy to elaborate on any particular part:

DashboardDude

@hunter_brown ,

Feel free to schedule some time. Might be easier just to see what you're working with than back and forth messaging: https://www.dashboarddudes.com/domo-help

hunter_brown

Thanks for solving, had some duplicate names and unnecessary columns and John the dashboard dude helped me fix them. Thanks again!

DashboardDude

Hi @hunter_brown ,

I tried to follow, but it's a bit hard without as much context: https://www.loom.com/share/0722862907b5443a8472479f8267beff

Feel free to book 30 with me and I can see if I can help: https://www.dashboarddudes.com/domo-help

hunter_brown

https://community-forums.domo.com/main/discussion/comment/85230#Comment_85230

Hey thanks for the quick response. Your thinking of seperating and appending is what I am doing. The 0 and 1 is no and yes for a credit card transaction. This leads me to think my way of thinking about appending is wrong.

The issue isn't with getting the details merged with the rest of the expenses so much as it is that the totals are wrong and I am trying to validate where it has gone wrong. Below is a screenshot of my join and then my append:

Hope this helps give some context.

DashboardDude

Hi @hunter_brown ,

Oh okay helps a bit more. For append, can you choose "include all columns" instead of "create transaction type (CC)" and see what that does to your numbers?

hunter_brown

https://community-forums.domo.com/main/discussion/comment/85237#Comment_85237

So I have been validating back and forth, and it turns out that not every transaction id, GL and posted date are unique.

What is happening is that post-ETL, the transactions are summed almost as if they had been grouped, even though I don't have a group by. So in the source expense file, there will be two transactions, one for 500 and one for 15 dollars. In the ETL, there will be two transactions for 515 dollars.

This indicates to me I need more join keys to ensure it is unique, would you agree?

hunter_brown

@DashboardDude I would appreciate some more assistance on this to get to a unique key. I want to use negative line amounts from CC details to match to credits (so I would have to make credit negative), and the rest of the line amounts to line up to debits. Do you know how to tackle something like this?

DashboardDude

@hunter_brown ,

Feel free to schedule some time. Might be easier just to see what you're working with than back and forth messaging: https://www.dashboarddudes.com/domo-help

hunter_brown

Thanks for solving, had some duplicate names and unnecessary columns and John the dashboard dude helped me fix them. Thanks again!