1 Reply Latest reply: Mar 19, 2012 4:02 PM by Brandon Apperson RSS

    Need help to join three tables

    Joakim Wasseng
      orderNocustomerdeliveryDate
      11012011-02-10
      21012012-03-11
      31022012-03-10
      41022013-04-15
      51032013-04-16

       

      customercustomerGroup
      1011
      1021
      1032

       

      customerGroupYearBonus
      120111,5%
      120122,0%
      120132,0%
      220110,5%
      220120,75%
      220131,2%


      Ok, so I have three tables that I need to JOIN and need help to figure out how to do it in QV.

       

      Its easy enough to LEFT JOIN table2 onto table1 since they have field "customer" in common.

       

      But then I need to LEFT JOIN table3 onto table1 using field "customerGroup" that only exists in table2 AND check that the table3.Year" = Year(table1.deliveryDate)

       

       

      All fields are QUALIFIED and I create my own UNQUALIFIEDkeys between the the three tables...

       

      Hope you understand

        • Re: Need help to join three tables
          Brandon Apperson

          Here is what I did based on your examples given, making inline tables for the data above:

           

          Table1:

          LOAD * Inline [

          orderNo, customer, deliveryDate

          1, 101, 2011-02-10

          2, 101, 2012-03-11

          3, 102, 2012-03-10

          4, 102, 2013-04-15

          5, 103, 2013-04-16

          ];

           

          left join

           

          LOAD * Inline [

          customer, customerGroup

          101, 1

          102, 1

          103, 2

          ];

           

          Table2:

          LOAD *,

          YEAR(deliveryDate) as deliveryYear

          Resident Table1;

           

          drop table Table1;

           

          left join

           

          LOAD * Inline [

          customerGroup, Year, Bonus

          1, 2011, .015

          1, 2012, .02

          1, 2013, .02

          2, 2011, .005

          2, 2012, .0075

          2, 2013, .012

          ]

          ;

           

          Table3:

          LOAD *,

          ' ' as Junk

          Resident Table2

          WHERE deliveryYear = Year;

           

          drop table Table2;

           

           

          The resulting data set you are looking for it in Table3.

           

          I also attached the .qvw for additional reference.

           

          Hope this helps!