topic Re: Merge two tables with duplicate data in QlikView

Merge two tables with duplicate data

Wed, 25 Nov 2020 16:16:04 GMT

I have 2 tables: Applicants and Company, and am thinking of the best way to link these tables

Applicants:

EmployeeID

EmployeeName

CompanyID

JobID

Company:

CompanyID,

EmployeeID

EmployeeName

EmployeeRole (e.g Finance/HR/Operations/Manager/etc)

The Applicants table is my master list

The Company table may contain EmployeeID not in the Applicants table. I do not need these data if the EmployeeID does not exist.

There are scenarios where multiple EmployeeIDs have the same CompanyID. For this case, I want to see the EmployeeRole as 'Applicant' instead of the default EmployeeRole value.

What I am trying to achieve is that when I click on an applicant name, my table updates to show the entire list of his/her company's employees and their roles.

Please advise on how should I proceed with this? I am thinking of using the CompanyID as the unique key between the two tables, though it appears there will be synthetic keys generated due to the repeated field names

Re: Merge two tables with duplicate data

nagarjuna_kotha — Mon, 14 Nov 2016 04:27:19 GMT

i did not understand ur question,

Could please elobaerate ur issue along with sample data and expected output.

Create 10 line of data that explains ur issue and share he file with expected output, that iss the best foe getting quick answers from community, if u explain verbally there a less chance to get ur answer.

So kindly share sample data.

-nagarjun

Re: Merge two tables with duplicate data

Mon, 14 Nov 2016 06:09:53 GMT

Hi Nagajuna

Apologies for the lack of info. I have added the 2 QVDs.

This table shows the list of applicants, and which company they are from.

This list shows the list of companies and the employees and roles, including those not in the applicant list.

What I am trying to achieve is that when I select EMP001, my dashboard table should update to show all employees from COMP1 (i.e. EMP001, EMP002 and EMP011). At the same time, the EmployeeRole for EMP001 and EMP002 should show as "Applicant", while EMP011 will continue to show as "Warehouse"

What I have currently in mind to do the selection will be that user will first select EMP001 (this will result in COMP1 being the only value shown in white in the filter box), followed by COMP1, then unchecking EMP001 to show all the EmployeeIDs in this company.

Do let me know if more clarification is needed

Re: Merge two tables with duplicate data

its_anandrjs — Mon, 14 Nov 2016 06:27:46 GMT

Try to load your table this ways

Emp_Job:
Mapping
LOAD EmployeeID&EmployeeName&CompanyID,
JobID
FROM
[..\..\Downloads\Qlik Comm\Applicants.qvd]
(qvd);

Main:
LOAD
CompanyID,
EmployeeID,
EmployeeName,
EmployeeRole,
ApplyMap('Emp_Job',EmployeeID&EmployeeName&CompanyID,'NA') as Job
FROM
[..\..\Downloads\Qlik Comm\Company.qvd]
(qvd);

Regards

Anand

Re: Merge two tables with duplicate data

Anonymous — Mon, 14 Nov 2016 06:41:58 GMT

Hi,

have a look at this post, it shoud help: Understanding Join, Keep and Concatenate

Re: Merge two tables with duplicate data

Mon, 14 Nov 2016 06:44:08 GMT

Hi Thabang

Yes I have read through that