6 Replies Latest reply: Aug 12, 2014 8:18 AM by Sindhu Neelisetty RSS

    Issue with the Joins

    Sindhu Neelisetty

      Hi Friends,

       

      Currently I'm working on a scenario where the case is like, i have 3 excel sheets

       

      Emp_Skill

       

      EmpIDSkillLevel
      1C3
      1Java4
      1C++3
      2C4
      2Java3
      2C++2
      2XML3
      2php

       

      Project_Details


      ProjectSkillRequiredLevel
      SalesJava3
      FinanceJava3
      FinanceC++3
      SalesC2
      SalesC++3
      SalesHTML3


      Emp_Details


      EmpIDProject
      1Sales
      2Finance


      I need all the skills along with their Required Level and Level in both the Project_details and Emp_skill tables.

       

      Table Emp_Skill and Project_Details are geeting asscoiated by Emp_Details.

      Below is the code i used in QV

       

      Emp:

      Load EmpID, Skill

      from Emp_Skill;

       

      Left Join(Emp)

      Load EmpID, Project

      from Emp_Details;

       

      Outer Join(Emp)

      Load Project, skill, RequiredLevel

      from project_Details;

       

      If I try to do so, I unable to get the Skills that are in the Project_Details table, only the common and Emp_SKill table skill names are getting populated.

       

      Kindly help in the scenario.

       

      Thanks,

      Sindhu.

       

       

          

       


        • Re: Issue with the Joins
          Amit Saini

          Sindhu,

           

          Like this???

            • Re: Issue with the Joins
              Sindhu Neelisetty

              Hi Amit,

              Thanks for your immediate reply and it is helpful.

              But what If I try to apply some conditions on the Level Field of Emp_SKill and Required SKill of Project together.

               

              For that case, i should join the Required SKill to the Emp_SKill table and then only will I be able to make some conditions?

              Or is there any alternative??

               

              Actually, I have four value fields in Project sheet and one value field in Emp Sheet, and the cases are different with each other, so what could be best practice to achieve the cases

               

              Thanks,

              Sindhu

            • Re: Issue with the Joins
              Amit Saini

              Sindhu,

               

              You don't have to join anything else here, all tables are now linked . Could you please share the desired result you want through all these table, s that I can try this at my end.

               

               

              Thanks,

              AS

                • Re: Re: Issue with the Joins
                  Sindhu Neelisetty

                  Hi Amit,

                   

                  The output I want to see is a pivot table and the desired output is:

                   

                   

                  ProjectSkillIDNameRequiredMinLevelRequiredIdeal  LevelLevelGap(Expression)
                  SalesC1Ravi342        Red
                  C++1Ravi 234Green
                  Java1Ravi 243Amber
                  HTML1Ravi 131Amber
                  FinanceJava2Raj340Red
                  C++2Raj232Amber
                  HRJava3Ashok003Blue
                  People Management3Ashok344Green

                   

                  The RequiredMinLevel and RequiredIdealLeval are fetching from the Project Sheet.

                   

                  Name is fetching from the Employee sheet.

                   

                  Gap is the Condtion like

                  IF (Level<RequiredMInLevel---Red,

                  IF(Level>=RequiredIdealLevel--Green,

                  IF Level is Between Min And Ideal------- Amber

                  and if ReuiredMinLevel and RequiredIdealLevel =0 and Level>0--- Blue

                   

                  Thanks in advance.

                   

                  Sindhu.

                    • Re: Issue with the Joins
                      Srikanth P

                      Hi Sindhu, Try like below:

                       

                      TempEmpSkils:

                      LOAD EmpID, Skill, Level From EmpoyeeTable;

                       

                      Left Join (TempEmpSkils)

                      LOAD EmpID, Project From EmPProjectTable:

                       

                      EmpSkills:

                      LOAD EmpID, Skill & '-' & Project AS ProjectSkillKey , Level Resident TempEmpSkils ;

                       

                      DROP Table TempEmpSkils ;

                       

                      PrijectDetails:

                      LOAD Skill & '-' & Project AS ProjectSkillKey , Skill, Project , RequiredMinLevel, RequiredDealLevel From ProjectDetails;

                       

                      These 2 tables are linked on ProjectSkillKey and we will get the desired Pivot table output.

                       

                      If not, Please provide the all the fields data.

                        • Re: Issue with the Joins
                          Sindhu Neelisetty

                          Hi Dathu,

                           

                          Sorry for the late reply.

                           

                          I was able to resolve the issue with the idea of creating the unique key with Skill and Project, where I have also included the EmpID. With few other logic's to the scripting I was able to finish the report.

                           

                           

                          Thanks for your help.

                           

                          Regards,

                          Sindhu