Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
124psu
Creator II
Creator II

circular join/reference in qlik sense?

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. 

qlik example.PNG

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?

Labels (3)
6 Replies
rogerpegler
Creator II
Creator II

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
MVP
MVP

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
Creator II
Creator II
Author

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
MVP
MVP

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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