Resident load from multiple tables

[#1]

Hello, I'm utterly confused by my current problem. Please help.

I've got three tables: Contract, Unit and Building:

I'm aiming at a table that includes the number of the active contracts per month:

So I've tried the following, but it let to a loop-warning:

NoConcatenate

temp1:

LOAD

    Contract.id,

    Contract.month,

    Contract.isVacant

RESIDENT Contract;

INNER JOIN(temp1) LOAD Building.id RESIDENT Building;

VacantContractsPerSegment:

LOAD

    Count(Contract.isVacant) AS NumberOfVacantContracts,

    Contract.month,

    Segment.id

RESIDENT temp1 group by Contract.month, Segment.id;

DROP TABLE temp1;

[sunny_talwar]

May be do like this:

NoConcatenate

temp1:

LOAD

    Contract.id,

    Contract.month,

    Contract.isVacant

RESIDENT Contract;

INNER JOIN(temp1) LOAD Building.id RESIDENT Building;

Left Join (Building)

LOAD

    Count(Contract.isVacant) AS NumberOfVacantContracts,

    Contract.month,

    Segment.id

RESIDENT temp1 group by Contract.month, Segment.id;

DROP TABLE temp1;

[#3]

This way, the first Join still leads to the Loop.

[sunny_talwar]

First join?

[Anonymous]

Check out, you can left/inner join both unit and building to temp1 depending on data structure and requirement

[Michael_Tarallo]

Hello D Kra,

I am just curious and my curiosity may be from my lack of knowledge on some of our scripting and expressions. But.... I would like to check anyway.

Why are you building a new table and using JOIN syntax in your script? Are you creating some sort of ETL process to create a QVD file of the combined data that then will be loaded into another app? I noticed you are at level 2 in the community so I am not sure how long you have been using Qlik products. The reason I ask is because when I was new to Qlik - I assumed I had to write SQL and combine tables and use JOINS etc.. like I was doing with a traditional BI tool and that was not the case. Qlik will automatically associate the tables on the same column name and put it into its in-memory associative engine. Then in the visualization itself you can use chart expressions and aggregations along with dimensions to create the chart. From my knowledge thus far, there is no reason to aggregate in the script unless you are creating an aggregate table or possibly for some other reasons. (Experts, please feel free to put me in my place 😉 )

I know that those expressions JOIN, RESIDENT and CONCATENATE etc are available for a number of scenarios, some complex and some not so. Again -just for my edification and trying to assist.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[#7]

Hey Michael Tarallo‌‌,
thank you for your thoughts on this problem. I'm still relatively new to the Qlik world but I've already worked with it and know the basic concepts (like the automatic association of columns). The reason for the above question was that I assumed that calculations done in the script resulted in a better performance than those in the charts, especially for great amounts of data. So I wanted to have the count of the vacancy-Contracts in the script instead of on the surface. That may as well be utter rubbish, so I'd be glad on further input on that matter.

[Michael_Tarallo · Accepted solution]

Hi D Kra,

It is actually more performant and better to load the data into Qlik first and then perform on the fly calculations with the chart objects vs aggregating in the script. Instead the chart objects and QIX engine will calculate on the fly aggregations which happen with every click and it does not go back to the database each time.

Let me see if I can get you an official article on how qlik works internally.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

[#9]

I'll do some research on the performance issues mentioned. If you have a commendable article at hand I'd be grateful for a link.

Thank you very much for your answers.

[garyhowjtc]

‌Hi Mike, I wonder if you can help to look at this thread Re: Create calculated dimension in load script from an associated table in data manager?

Greatly appreciated the clues here.