circular join/reference in qlik sense?

[124psu]

I am hoping someone can guide me in the right direction for my problem.

I am fairly new at qlik so please bear with me. I have 2 tables that I have brought into qlik. All the data is validated and accurate when creating my charts. 

----------------------------

[ASD_DOLLAR_INFO]:
LOAD
[INFO_ID],
[STATUS],
[ASD_TITLE],
[ASD_SUB_TITLE],
[CALENDAR_YEAR],
[START_YEAR],
[END_YEAR],
[REVENUE],
[BUDGET],
[BUDGET_TARGET]
FROM [lib://MYFILEPATH/ASD_DOLLAR_INFO.qvd]
(qvd);

[ASD_DETAILS]:
LOAD
[DETAILS_ID],
[CALENDAR_YEAR] as [ASD_DETAILS.CALENDAR_YEAR]
[CLIENT_TITLE],
[CLIENT_COMPANY],
[CLIENT_SUB_ACCOUNT_TITLE],
[CLIENT_START_DATE],
[CLIENT_END_DATE]
FROM [lib://MYFILEPATH/ASD_DETAILS.qvd]
(qvd);

Now -- I have another table I need to bring in which tracks additional details that have a mix of common fields from the 2 tables above. But once I bring in this table, it seems to throw off the numbers and virtually all my dollar amounts are showing as 0. 

[ADDITIONAL_DETAILS]:
LOAD
[CALENDAR_YEAR],
[DOCUMENT_TITLE],
[CURRENT_ESCROW_AMOUNT],
[MAX_ESCROW_AMOUNT],
[MIN_ESCROW_AMOUNT],
[AVG_ESCROW_AMOUNT],
[BEGINNING_YEAR],
[COMPLETED_YEAR],
[CLIENT_TITLE],
[CLIENT_COMPANY],
[CLIENT_SUB_ACCOUNT_TITLE],
[CLIENT_START_DATE],
[CLIENT_END_DATE]
FROM [lib://MYFILEPATH/ADDITIONAL_DETAILS.qvd]
(qvd);

I was assuming qlik would understand the associations. You can see my last table (details table) has many matching columns from tables one and two. I have a png below to show a lay out of my 3 tables and how I thought qlik would handle the associations. 

I need to change the alias in table 3 ((additional_details)) in order to match the field name from table 1 since that is what I am wanting to associate with. But as stated, once I bring in table 3 (additional_details) it throws the facts/measures. Any suggestions on what I am doing wrong here?

[rogerpegler]

Qlik's associate model won't allow circular references.  For example for a given INFO_ID, there are one or more associated DETAILS_ID. For each of those there are CLIENT_SUB_ACCOUNT_TITLE which are associated with one or more of the same in ADDITIONAL_DETAILS. For each of those there are BEGINNING_YEAR and COMPLETED_YEAR. If you try to associate those back to START_YEAR and END_YEAR you are potentially associating with records that are not the same as the original INFO_ID.

The appropriate solution depends on the nature of the data.  If the intent is that for each CLIENT there are multiple DOLLAR_INFO and ADDITIONAL DETAILS (one for each year), then you may be able to :

Left Join (ASD_DOLLAR_INFO)

Load DETAILS_ID as INFO_ID, CLIENT_SUB_ACCOUNT_TITLE Resident ASD_DETAILS;

Left Join (ASD_DOLLAR_INFO)

Load *, BEGINNING_YEAR as START_YEAR, COMPLETED_YEAR  as END_YEAR Resident ADDITIONAL_DETAILS;

Drop Table ADDITIONAL_DETAILS;

Drop Fields BEGINNING_YEAR , COMPLETED_YEAR;

[Vegar]

In Qlik Sense all data is loaded into memory and "active" all the time. Therefore all links are active all the time, circular references have the potential to reduce your associating logic down to nothing by

Consider the data below.

Table 1

%EMPLOYEE, %CLIENT, VALUE

Vegar, Acme, 2019

Vegar, Pharmacia, 1998

Table2

%EMPLOYEE, ADDRESS, COUNTRY

Vegar, Uppsala, Sweden

Table3

%CLIENT, COMPANYNAME, ADDRESS, COUNTRY

AcmeInc, Acme Inc., Gdansk, Poland

Pharmacia, Pharmacia ab, Uppsala, Sweden

What will happen when a user select the COMPANYNAME Acme Inc.?

Acme Inc is associated with  one row in Table 1 (%CLIENT = AcmeInc) 

the Table 1 row is connected to a row in Table 2 (%EMPLOYEE= Vegar).

The row in Table 2 is associated Table 3 (COUNTRY= Sweden), but in my initial selection I reduced my COUNTRY down to Poland !! 🙀

You will have no associated calories left in your data because of this circular reference. 

This is why you have to choose where your table 3 should connect to your data model. Pass you should rename some fields if they are important to your model.

In my example above I could have renamed my Table 3 ADDRESS and COUNTRY to CLIENT ADDRESS and CLIENT COUNTRY to avoid this issue.

[124psu]

Thank you for your insight. Another question in mind - I am now going to instead combine the tables 'ASD_DOLLAR_INFO' and 'ASD_DETAILS' into one qvd then bring in the third qvd/tale 'ADDITIONAL_DETAILS' in order to find its associations. My new problem arises when I am trying to concatenate the first 2 tables. I am first bringing in the 2 separate qvds and creating an association in the data manager and then concatenating the 2 files so that they are now consolidated into one for better management. I am now getting numbers that are off but not really off too much, really insignificantly when I compare to my sql query. Any help?

----------- qlik concat ----------

[ASD_DOLLAR_INFO]:
LOAD
[INFO_ID] as [INFO_ID-DETAILS_ID],
[STATUS],
[ASD_TITLE],
[ASD_SUB_TITLE],
[CALENDAR_YEAR],
[START_YEAR],
[END_YEAR],
[REVENUE],
[BUDGET],
[BUDGET_TARGET]
FROM [lib://MYFILEPATH/ASD_DOLLAR_INFO.qvd]
(qvd);

Concatenate ([ASD_DOLLAR_INFO])
[ASD_DETAILS]:
LOAD
[DETAILS_ID] as [INFO_ID-DETAILS_ID],
[CALENDAR_YEAR]
[CLIENT_TITLE],
[CLIENT_COMPANY],
[CLIENT_SUB_ACCOUNT_TITLE],
[CLIENT_START_DATE],
[CLIENT_END_DATE]
FROM [lib://MYFILEPATH/ASD_DETAILS.qvd]
(qvd);

Now -- When I validate my data on qlik's front end vs my query in the database I am getting different numbers.

------------ sql ----------

select calendar_year, sum(revenue)
  from ASD_DOLLAR_INFO a
    inner join ASD_DETAILS b
      on a.INFO_ID = b.DETAILS_ID
    group by a.calendar_year
   order by 1;

My sql query is accurate as I am conducting an inner join between these 2 tables by the correct column/fields. Can you point out where I am going wrong? I'm positive it is coming from the qlik side.. thank you.

[Vegar]

Concatenate and join are two different things.

To accomplish what your do in three sql you should use join, not Concatenate.

ASD_DOLLAR_INFO]:
LOSS
[INFO_ID] as [INFO_ID-DETAILS_ID],
[STATUS],
[ASD_TITLE],
[ASD_SUB_TITLE],
[CALENDAR_YEAR],
[START_YEAR],
[END_YEAR],
[REVENUE],
[BUDGET],
[BUDGET_TARGET]
FROM [lib://MYFILEPATH/ASD_DOLLAR_INFO.qvd]
(qvd);

INNER JOIN ([ASD_DOLLAR_INFO])
[ASD_DETAILS]:
LOAD
[DETAILS_ID] as [INFO_ID-DETAILS_ID],
//[CALENDAR_YEAR] 
[CLIENT_TITLE],
[CLIENT_COMPANY],
[CLIENT_SUB_ACCOUNT_TITLE],
[CLIENT_START_DATE],
[CLIENT_END_DATE]
FROM [lib://MYFILEPATH/ASD_DETAILS.qvd]
(qvd);

[124psu]

I've tried doing an inner join exactly how you had mentioned but no luck there. I do understand the difference now, but the whole point of concatenating was to consolidate/merge 2 qvd into one qvd to make it more manageable and also to work around my circular join issue. 

[124psu]

Vegar, my apologies, I forgot to update my qvd files and yes the inner join is working just fine now.