4 Replies Latest reply: Mar 22, 2012 2:02 PM by savi aluthgama savi RSS

    how to join this two tables

      There are two tables call “item ” and “Working Hours ”. I want to join these table using QlikView.

      I want to calculate the number of hours with in the dates ofMax_date and Min_date.

      plz hepl me.........

       

       

      thanx

      Savi

        • Re: how to join this two tables

          I think this might be what you are looking for.  It depends on whether you want to join on both Factory and Team or just Factory.  I joined on both.  I also assumed you wanted exact matches only thus the inner join.  If you want everything from Item_Make regardless of what is in WorkingHours then you would want to left join.  If you want everything from WorkingHours regardless of what is in Item_Make you would want to right join.

           

          If you do not want to join on both Factory and Team then you should rename one of the Team fields so QlikView does not make a link.

           

          Table1:

          LOAD Factory,

               max_date,

               min_date,

               Team

          FROM

          C:\Users\BApperson\Desktop\Item_Make.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

          inner join

           

          LOAD date_,

               factory as Factory,

               hourse,

               Team

               //F5

          FROM

          C:\Users\BApperson\Desktop\workingHours.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

           

          TableFinal:

          LOAD *,

          ' ' as Junk

          Resident Table1

          WHERE date_ >= min_date and date_ <= max_date;

           

          drop table Table1;

           

          This gives you the hours where date is between min date and max date.

           

          Hope this helps!

           

          I also attached a QV document in case you need extra reference.

            • how to join this two tables

              Thanks bapperson

              Actually I wan to this kind of thing. Plz give me a help ifu don’t mind.

              In item maker table contain, how many dates take to manufacture that item.

              Eg:

              Tiem1: 

              Max_date = 20/01/2012

              min_date =01/01/2012 

              So total dates take for Item1 to manufacture =20.

               

               

               

              In working hours, table contain the all daily working house.I want to calculate number of  hours to manufacture for Item 1.

              Eg:

              01/01/2012 (Min_date) : 10Houre

              02/01/2012                         :11Houre

              03/01/2012                         :14Houre

              .

              .

              .

              20/01/2012(Max_date): 9 Houre

               

              Total houre  :  sum(hours) where   Min_date  <=   date_   <= Max_date

               

              thanks

              SAVI

            • how to join this two tables

              where are you getting the hours from. Is it from the spreadsheet or do u need to calculate that from max and min dates?