Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Issue with the Joins

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.

    


Tags (1)
6 Replies
amit_saini
Honored Contributor III

Re: Issue with the Joins

Sindhu,

Like this???

Not applicable

Re: Issue with the Joins

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

amit_saini
Honored Contributor III

Re: Issue with the Joins

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

Not applicable

Re: Re: Issue with the Joins

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.

Not applicable

Re: Issue with the Joins

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.

Not applicable

Re: Issue with the Joins

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

Community Browser