Need really help; loading/joining data

amirmohamed — Sat, 16 Nov 2024 21:20:52 GMT

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

Re: Need really help; loading/joining data

marcus_sommer — Wed, 27 Feb 2019 13:06:18 GMT

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

Re: Need really help; loading/joining data

amirmohamed — Thu, 04 Apr 2019 10:27:48 GMT

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.

topic Re: Need really help; loading/joining data in App Development

Need really help; loading/joining data

Re: Need really help; loading/joining data

Re: Need really help; loading/joining data