topic Re: Issue with the Joins in QlikView

Issue with the Joins

Mon, 04 Aug 2014 10:45:24 GMT

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.

Re: Issue with the Joins

amit_saini — Mon, 04 Aug 2014 10:55:58 GMT

Sindhu,

Like this???

Re: Issue with the Joins

Mon, 04 Aug 2014 11:39:49 GMT

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 — Mon, 04 Aug 2014 11:46:30 GMT

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,

Re: Re: Issue with the Joins

Mon, 04 Aug 2014 12:02:32 GMT

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.

Re: Issue with the Joins

Mon, 04 Aug 2014 17:26:23 GMT

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

Tue, 12 Aug 2014 12:18:20 GMT

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