Hi there, new to the community and trying to build my first model.
I keep getting a problem where I get a data loop. I have read up about this and understand the issue but cannot seem to get my head around how to solve the problem.
I have 3 tables:
TABLE 1: [CUSTOMER_HIGHLANDER]
MS SQL Table with dimensions:
[CUSTOMER_KEY]
,[ACCOUNT_NUMBER]
,[CUSTOMER_NAME]
,[CUSTOMER_ACTIVITY_DESC]
,[STATUS_DESC]
,[SUB_STATUS_DESC]
,[PRODUCT_DESC]
,[PRODUCT_MIDDLE_DESC]
,[SUB_PRODUCT_DESC]
,[CUSTOMER_SEGMENT_LEVEL_1]
,[CUSTOMER_SEGMENT_LEVEL_2]
,[CUSTOMER_SEGMENT_LEVEL_3]
,[RELATIONSHIP_DESC]
,[TOWN]
,[SUBURB_NAME]
,[SUPER_REGION]
,[BRANCH_NAME]
,[ABSA_SUPER_REGION]
,[ABSA_REWARDS_DESC]
TABLE 2: [HIGHLANDER_FINANCE]
Also a MS SQL table with measures
[CUSTOMER_KEY]
,[ACCOUNT_NUMBER]
,[COST_CENTRE_CODE_1]
,[INFORMATION_DATE] (This is monthly data so as at month end)
,[NON_INTEREST_INCOME]
,[NET_INT_INCOME_ON_ADV_AND_DEP]
,[TOP_LINE_INCOME]
,[TAXATION_EXPENSE]
,[PROFIT_LOSS_AFTER_TAX]
,[ACCOUNT_BALANCE]
,[ORIGINAL_CAPITAL_AMT]
I created a concatenated field on CUSTOMER_KEY and ACCOUNT_NUMBER (CUST_ACCNT_NUMBER) and joined these 2 tables. That was no problem.
These SQL tables show the situation as is. The part I am struggling with is to bring in the 3rd table (BUDGET_DATA) which shows the budgetted figures in XLSX format. Eg I want to be able to show number of accounts budgeted vs actual. Or Non-interest-income budgeted vs actual.
The fields in this data are:
| INFORMATION_DATE |
| Product |
| Sub-Product |
| Product_Mapping |
| BB/Retail/Non-segment |
| Classification |
| Segment_Mapping |
| PRODUCT_SEGMENT_DATE |
| # of Clients |
| # of Accounts |
| # of Accounts Retail Transactional NIR |
| Ave Debit Balance |
| Ave Credit Balance |
| NII |
| NIR |
This data is aggregated on a high level by month, product and customer_segment. Unfortunately the product and segment don't match up with any fields on the existing SQL tables.
So what I elected to do was to create a PRODUCT_MAPPING and SEGMENT_MAPPING column on the [CUSTOMER_HIGHLANDER] data. These columns create a PRODUCT that matches up with the PRODUCT_MAPPING on BUDGET_DATA and SEGMENT that matches up with the SEGMENT_MAPPING on BUDGET_DATA. But this data is also aggregated by MONTH.
When I try to bring this data into the model, I get an error saying I have a loop.
I am really struggling to figure out how to structure my data so I can show for example by date by product showing number of customers. I'd appreciate any tips or suggestions!
