8 Replies Latest reply: Sep 20, 2016 4:34 AM by a b RSS

    Resident load from multiple tables

    a b

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

       

       

       

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

       

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

      Ausschnitt.PNG


      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;

        • Re: Resident load from multiple tables
          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;

          • Re: Resident load from multiple tables
            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

              • Re: Resident load from multiple tables
                a b

                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.

                  • Re: Resident load from multiple tables
                    Michael Tarallo

                    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