I have two data sets that are based on the same underlying information but I unfortunately do not own them so cannot write a report to get all the elements I need in a single report. Data A shows transactional sales details which include a breakdown of sales and taxes. Data B shows transactional sales details by tender type. I need to be able to match transactions from data set A to data set B so I can ascribe the tender type to each transaction and the split between sales and sales tax. I am able to do this in most cases using a join function except when multiple tender types were utilized. For example someone paid for their order with both credit and cash. In those cases, Set A duplicates in the join so I end up with too many transactions that will require significant manipulation on the back end.
Sample data and the desired output are attached.
Hi @Dsmithhisler ,
Have you tried using a summarize tool after Set B to sum the transaction amount by Date, Store ID & Check Number? I think that would fix your problem. Something like this:
Joining the 2 sets of data together on - Date to Tender_date / Store_id to Tender store id / check-num to Check number - creates the file format you are looking for then chasing that with a multi row formula on each fields you want to reflect the second row to reflect null on will do the trick.
Thank you for the reply but the summarize unfortunately loses the description for tender type which I need to be able to retain. I think I have it figured out now though using a Unique tool to remove the duplicates, joining what is left, and then appending with the duplicate values.