Need really help; loading/joining data

[amirmohamed]

Hi All, 

I have spent a lot of time on this issue and I am really stuck and I need your help 

I have 4 tables 

Calendar
calendar_date (has all dates)
year
period

Sales
calendar_date (has dates up to Today)
customer_code (specific customer codes where we made sales for)
net_sales
gross_sales

Budget
customer_group (all customer groups)
period
year

Customer
customer_code (all customer codes)
customer_group (all customer groups)

The above are the four tables, and the issue i am having is;

I need to create a link between them, we have a link that is working fine but the Budget data brings only for the current period, as the sales table has specific dates and customer codes, so it reduces the data for all

Existing Model: If there is no Sales in a month for a customer, then no budget (target) is not showing on the reports. (Sales joins Customer joins Target)

New Model: If we create link between Calendar and Target (blue line) then it creates circular reference?

what i am trying to get is; regardless of dates in Sales or customer_code exist or not I want to show the budget based the Customer group but when a year, period is selected then it filters for this period or year

I have tried new link table, concatenate data , join data to sales but none has worked

I need your help

[marcus_sommer · Accepted solution]

The easiest approach for such a usecase is just to concatenate sales and budget. In your case you adds the customer group per join/mapping from your customer table to sales and creates per makedate(year, period, 1) a date within the budget-load.

- Marcus

[amirmohamed]

Hi Marcus,

Thank you this has helped me a lot and solved my issue even though I had to add few changes but it resolved the issue.