Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
scooterza
Contributor
Contributor

How to structure my data to avoid loops

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!

2 Replies
dwforest
Specialist II
Specialist II

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.

bill_markham
Champion III
Champion III

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.