Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
The short answer would be to create a Master Calendar (search, there are plenty examples of scripts to create).
The calendar has the different time periods defined for each day in the calendar, so you can hook based on months, quarters, etc.
Maybe concatenate the HIGHLANDER_FINANCE and BUDGET_DATA together to create a single fact table with a month level granularity, leaving CUSTOMER_HIGHLANDER as a single dimension table.
For those not yet fully conversant with Qlik concatenating facts into a single Fact table can seem weird, but it usually results in an efficient star schema and eliminates issues re loops and synthetic keys.