I have a data file that I get from our third party online sales system; I have no control over this data, and it looks like this in one big table:
SaleDate, SaleTime, ProductName, Qty, NetRevenue, CustomerInfo, plus lots of other minor data
I get one row for each ProductName, even if a customer purchased multiple products on one transaction. As a result, there could be many rows for the same SaleDate and SaleTime. What I need to do is create an output that contains one row for each date, with all quantity and revenue for each product on that date. Assuming we have two products, FOO and BAR, I'm looking for something like this as the final output:
DATE FOO-UNITS FOO-REVENUE BAR-UNITS BAR-REVENUE
02-20-2018 10 100 7 84
02-21-2018 5 50 11 132
The only way I've been able to come up to do this so far is to add four fields per product to my database:
calcFooQuantity: if ( ProductName = "FOO" ; Qty ; 0 )
calcFooTotalQuantity: Total of calcFooQuantity, running, restart when sorted by SaleDate
calcFooRevenue: if ( ProductName = "FOO" ; NetRevenue ; 0 )
calcFooTotalRevenue: Total of calcFooRevenue, running, restart when sorted by SaleDateI then created a report sorted by SaleDate, and added the two running total fields, and it works as expected.
The problem is that this is absolutely unmanageable, because we don't have two products, we have twelve products—I'd need 48 fields…and adding a new product means four new fields. It also just feels kludgy as heck. Is there some way to use relationships and additional tables to make this simpler? My thought is a table for Products, obviously, but I fail when I start trying to figure out what that table looks like.
I can't use sub-summaries, I don't think, because I need the data to appear to be one row in the output—it's going to be exported to Excel via a script as the final step.