6 Replies Latest reply: Jan 6, 2015 2:45 AM by Colin Albert RSS

    Join two tables into other

    Андрей Шепель

      Good day.

       

      in load script i load two tables from xlsx files:

       

      WorkingDays:

      LOAD

          Work_schedule,

          Дата,

          Status

      FROM

      $(DataPath)WorkingDays.xls

      (biff, embedded labels, table is [WDays$])

      where Дата < today(1);

       

      WorkingShedule:

      LOAD

        Distributor_Id,

        Work_schedule

        FROM

      $(DataPath)WorkShedule.xls

      (biff, embedded labels, table is [WShedule$]);

       

       

      They have the same field Work_schedule.

      Tell me please, how to join these two tables into third (other table) by field Work_schedule and drop tables?

        • Re: Join two tables into other
          Harshal Patil

          WorkingDays:

          LOAD

              Work_schedule,

              Дата,

              Status

          FROM

          $(DataPath)WorkingDays.xls

          (biff, embedded labels, table is [WDays$])

          where Дата < today(1);

           

          WorkingShedule:

          LOAD

            Distributor_Id,

            Work_schedule

            FROM

          $(DataPath)WorkShedule.xls

          (biff, embedded labels, table is [WShedule$]);

           

          NOconcatenate

          Tbl3:

          load * resident WorkingDays;

          drop table WorkingDays;

           

          join(Tbl3)

           

          load * resident WorkingShedule;

          drop table WorkingShedule;

            • Re: Join two tables into other
              Prashant Sangle

              Hi,

               

              Try like,

              WorkingDays:

              LOAD

              Work_schedule,

              Дата,

              Status

              FROM

              $(DataPath)WorkingDays.xls

              (biff, embedded labels, table is [WDays$])

              where Дата < today(1);

               

              //WorkingShedule:

              join

              LOAD

              Distributor_Id,

              Work_schedule

              FROM

              $(DataPath)WorkShedule.xls

              (biff, embedded labels, table is [WShedule$]);

               

              NOconcatenate

              Table3:

              load * resident WorkingDays;

              drop table WorkingDays;

               

              Regards

            • Re: Join two tables into other
              Mario Estrada

              WorkingDaystmp:

              LOAD

                  Work_schedule,

                  Дата,

                  Status

              FROM

              $(DataPath)WorkingDays.xls

              (biff, embedded labels, table is [WDays$])

              where Дата < today(1);

              OUTER JOIN

              WorkingSheduletmp:

              LOAD

              Work_schedule

                Distributor_Id

                FROM

              $(DataPath)WorkShedule.xls

              (biff, embedded labels, table is [WShedule$]);

               

              finaltable:

              load * resident WorkingDaystmp;

              outer join

              load * resident WorkingSheduletmp;

              drop table WorkingDaystmp;

              drop table WorkingSheduletmp;

              • Re: Join two tables into other
                Colin Albert

                You do not need a join.

                The two tables have a common field Work_schedule, you can just load each table and QlikView will automatically associate the two tables on the common field name.

                 

                You rarely need to use joins in QlikView.

                  • Re: Join two tables into other
                    Colin Albert

                    A better solution would be to use a Mapping Table because the working schedule table only has two columns.

                    The mapping table is automatically deleted when the script finishes leaving just the WorkingDays table.

                     

                    WorkingShedule_map:

                    mapping

                    LOAD

                      Work_schedule,

                      Distributor_Id

                    FROM

                    $(DataPath)WorkShedule.xls

                    (biff, embedded labels, table is [WShedule$]);

                     

                    WorkingDays:

                    LOAD

                        Work_schedule,

                        ApplyMap('WorkingSchedule_map', Work_schedule) as Distributor,

                        Дата,

                        Status

                    FROM

                    $(DataPath)WorkingDays.xls

                    (biff, embedded labels, table is [WDays$])

                    where Дата < today(1);