AnsweredAssumed Answered

Help with table/field structure

Question asked by griffman on Jun 23, 2018
Latest reply on Jun 23, 2018 by ThierryGuemboura

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

etc...

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 SaleDate

I 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.

 

thanks!
-rob.

Outcomes