3 Replies Latest reply: Aug 27, 2016 4:11 AM by Gysbert Wassenaar RSS

    Left Joins without writing load script?

    josh flory

      I see that Qlik has some data load editor scripting language options to handle joins...however, when that happens it appears that Qlik does a sort of in-memory ETL instead of loading each table separately as it does in the Data Manager interface where you click ADD Data.

       

      So my question is this:

       

      I have an Employees table and an EmployeeLeaveTaken table.

       

      I want to bring both in my model but EmployeeLeaveTaken is Zero to Many on the Employees table.

       

      An Employee could have NEVER taken leave (zero) of could have taken 100 instances of leave (or a thousand or...whatever).

       

      So an INNER JOIN (the default join type) doesn't really work here as I need to see ALL Employees even if they have not taken leave.

       

      Unless I am missing something obvious here???

       

      Thanks!

        • Re: Left Joins without writing load script?
          Gysbert Wassenaar

          The Data Manager won't join the tables. The result of joining two tables is one table. If you look at the Data model viewer you'll probably see separate tables that are associated (linked) with each other on the common field names. If you want to join tables to create a new table then you'll have to use the Data load editor. You can unlock the section the Data Manager created and put a join in between:

           

          TableA:

          LOAD .... etc... FROM ...sourceA...;

           

          LEFT JOIN (TableA)

           

          LOAD ...etc...FROM ...sourceB... ;

           

          The result will be one table called TableA with all the records from Table A and the fields from the other table from the records that matched with TableA.

            • Re: Left Joins without writing load script?
              josh flory

              OK I think the issue is my lack of understanding in exactly how the tool handles associated tables.

               

              When it shows an association does the tool always load both tables independently?

               

              THEN, when they are placed on the design surface in a chart/graph/etc... it is smart enough to know if it needs to return all records from both tables, from one, the other, or inner join etc so it can show NULL values?

               

              So if I had a count of EmployeeLeave taken and charted that against employees it would shows some employees took NO leave (as opposed to just reducing the employee count by not showing employees with no leave)?

                • Re: Left Joins without writing load script?
                  Gysbert Wassenaar
                  When it shows an association does the tool always load both tables independently?

                  Sorry, I don't understand what you're asking. Tables are always loaded independently, i.e. one at a time. After loading a table it is associated with other tables in the model using the fields the tables have in common. Does that answer your question?

                  THEN, when they are placed on the design surface in a chart/graph/etc... it is smart enough to know if it needs to return all records from both tables, from one, the other, or inner join etc so it can show NULL values?

                  Yes.

                  So if I had a count of EmployeeLeave taken and charted that against employees it would shows some employees took NO leave

                  Yes. See attached example