9 Replies Latest reply: Dec 11, 2013 8:27 AM by Dario Valsecchi RSS

    Left Outer Join with script

      Hi, can anyone help me!

      I have to transate the simple sql statement:

      Select A.*, B.SalaryGrade

      from A Left outer join B

      on a.Salary between B.MinSG and b:MaxSG

       

      I have try with "IntervalMatch" but i'm not able to have the final result.

       

      Simplifying I have table A:

      EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

      0000001, xxxxx, yyyyyy, 2013, 1, 45000

      0000001, xxxxx, yyyyyy, 2013, 2, 45000

      0000001, xxxxx, yyyyyy, 2013, 3, 50000

       

      And the Table B:

      SalaryGrade, MinSG, MaxSG

      Grade 01, 30000, 40000

      Grade 02, 40000, 45000

      Grade 03, 45000, 55000

       

      I want have the table C like this:

      EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary, SalaryGrade

      0000001, xxxxx, yyyyyy, 2013, 1, 45000, Grade 02

      0000001, xxxxx, yyyyyy, 2013, 2, 45000, Grade 02

      0000001, xxxxx, yyyyyy, 2013, 3, 50000, Grade 03

       

      Thank for help

        • Re: Left Join with script
          sasi k

          Hello Dario

          You better to use Intevelmatch() function here

           

          Table B:

          SalaryGrade, MinSG, MaxSG

          Grade 01, 30000, 40000

          Grade 02, 40000, 45000

          Grade 03, 45000, 55000

           

          Table A:

          EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

          0000001, xxxxx, yyyyyy, 2013, 1, 45000

          0000001, xxxxx, yyyyyy, 2013, 2, 45000

          0000001, xxxxx, yyyyyy, 2013, 3, 50000

           

          Table C:

          IntervalMatch(GrossSalary) Load MinSG,MaxSg resident Table B;

           

           

          Hope you can get with this ...

            • Re: Left Join with script

              Hi Sasi,

              thank for you reply but i don't want have two table with a link. I want have only one table.

              I have just try this solution

              and don't work fine GrossSalary 45000 is Grade02 and Grade03.

               

              A:

              load * inline

              [EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary

              0000001, xxxxx, yyyyyy, 2013, 1, 45000

              0000001, xxxxx, yyyyyy, 2013, 2, 45000

              0000001, xxxxx, yyyyyy, 2013, 3, 50000

              ];

               

              B:

              load * inline

              [SalaryGrade, MinSG, MaxSG

              Grade 01, 30000, 40000

              Grade 02, 40000, 45000

              Grade 03, 45000, 55000

              ];

               

              C:

              IntervalMatch(GrossSalary) Load MinSG, MaxSG resident B;

               

              Immagine.png

                • Re: Left Join with script

                  Dario,

                   

                  In fact you can create the IntervalMatch table and use it afterwards.

                  By using this function, you should not have overlapping grades, for example 45000 may belong to several grades.

                  grade 1 can finish at 45000 but grade2 must begin at 45001 or 45000.01

                   

                   

                  //SOLUTION 1 : problem, your tables must have very few rows because the first JOIN will create many rows (nb of rows of table 1 x nb of rows of table 2). But it is easy to understand. First you do a JOIN (with a lot of rows), after that you create the real table with a WHERE statement

                   

                  Join (Temp_TableA)
                  LOAD * Resident Temp_TableB;

                  TableA:
                  LOAD EmpCode, EmpName, EmpSurname, Year, Month, GrossSalary, SalaryGrade
                  Resident Temp_TableA
                  Where GrossSalary >= MinSG AND GrossSalary < MaxSG;

                  Drop tables Temp_TableA, Temp_TableB;

                   

                   

                   

                  // SOLUTION 2: with the IntervalMatch that populates a temp_tableC, that is used for the join with TableA

                  But you still don't have what you wanted first. I can do a secondary JOIN to get the SalaryGrade (I use the XX field I have create to do the JOIN)

                   

                   

                   

                  Temp_TableC:
                  INTERVALMATCH(GrossSalary)
                  LOAD distinct MinSG, MaxSG
                  Resident Temp_TableB;

                  LEFT Join (Temp_TableA)
                  LOAD GrossSalary, MinSG & '-'& MaxSG as XX
                  Resident Temp_TableC;

                  LEFT Join (Temp_TableA)
                  LOAD SalaryGrade, MinSG & '-'& MaxSG as XX
                  Resident Temp_TableB;

                  Drop tables Temp_TableB, Temp_TableC;
                  Drop field XX;

                   

                  Fabrice