9 Replies Latest reply: Mar 7, 2018 11:13 AM by Gary How 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