Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Currently I'm working on a scenario where the case is like, i have 3 excel sheets
Emp_Skill
EmpID | Skill | Level |
---|---|---|
1 | C | 3 |
1 | Java | 4 |
1 | C++ | 3 |
2 | C | 4 |
2 | Java | 3 |
2 | C++ | 2 |
2 | XML | 3 |
2 | php |
Project_Details
Project | Skill | RequiredLevel |
---|---|---|
Sales | Java | 3 |
Finance | Java | 3 |
Finance | C++ | 3 |
Sales | C | 2 |
Sales | C++ | 3 |
Sales | HTML | 3 |
Emp_Details
EmpID | Project |
---|---|
1 | Sales |
2 | Finance |
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.
Sindhu,
Like this???
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
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
Hi Amit,
The output I want to see is a pivot table and the desired output is:
Project | Skill | ID | Name | RequiredMinLevel | RequiredIdeal Level | Level | Gap(Expression) |
---|---|---|---|---|---|---|---|
Sales | C | 1 | Ravi | 3 | 4 | 2 | Red |
C++ | 1 | Ravi | 2 | 3 | 4 | Green | |
Java | 1 | Ravi | 2 | 4 | 3 | Amber | |
HTML | 1 | Ravi | 1 | 3 | 1 | Amber | |
Finance | Java | 2 | Raj | 3 | 4 | 0 | Red |
C++ | 2 | Raj | 2 | 3 | 2 | Amber | |
HR | Java | 3 | Ashok | 0 | 0 | 3 | Blue |
People Management | 3 | Ashok | 3 | 4 | 4 | Green | |
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.
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.
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