Data Structure Problem - Can't join properly

[#1]

1. We have an archaic system built on S36 RPGII.   It is a batch processing system that is not built on top of an relational DB.
2. We have been able to write a program that does take the flat files of our invoicing system and create columnar data.
3. I have created a composite key to join all the "tables" to recreate the invoices and perform calculations on the data.
   • The key is comprised of  invoicedate&orderno.   We cannot simply use order # as a key because our system will re-use order numbers about every 3-5 years based on volume of sales in a particular area
4. For all the data that is 1:1, I do Left Joins to reduce that structure to 1 table.   The rest of the tables are 1 to many.
5. Currently as it sits, my data balances to our system sales reports EXCEPT for another set of data  that needs to be incorporated.  
   • This data does not come from the "table structure" and comprises of adjustments
   • This data can be processed at any point in the fiscal year and gets applied directly as a sales or margin adjustment in Sales History.
     • This adjustment will show up in our Sales by Vendor, Customer Sales  and Salesperson Sales

PROBLEM:   Since adjustment data does not have a "KEY", it cannot be logically joined to the regular "tables"

In my header, we have Customer#, Vendor# and Sales# as well as OrderNo but Invoice date only post the Month and Year, not the day as well as the table for the adjustments.

If I join on these items, I  get a loop because of the Salesperson table is not part of the header.   Our orders can have multiple salespeople with an allocated percent of the sale.

If I join the Salesperson to the OrderHeader, my calculations go way out of wack and my application no longer balances, but I can join in the adjustment data although it creates a SYNTH Key

The scripts and app is quite lengthy and convoluted, so I am not sure the best way to share - hopefully someone out here understands my dilemma.

I have attached a table diagram

[Peter_Cammaert]

I think this is a case for a Link Table with slowly changing dimensions.

Am I correct in assuming that your set-up is comparable to the combination of Sales Data (daily) with Plan information that is aggregatesd across all divisions, products and regions (so no detail level) and runs for MonthYear periods only?

[#3]

Kind of - we tally sales by customer, vendor and salesperson.   I can aggregate sales data directly from the invoice data.  We do international import and export.

As an example of an adjustment, we need to cost our purchases and calculate sales margins, so when we invoice we will put down an estimated cost for shipping or wharfage, customs etc.    The overseas supplier may not ship for 3 months and rates/fees may change or the bill may come in 1-2 months after the invoice.   Once we reconcile the costs, they get applied to the months sales as a straight Margin adjustment.

So yes, these adjustments come in the MMYY timeframe, but do get applied to the original salesperson/vendor/customer 

[#4]

I'll take a look at this tomorrow

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions