topic Re: How to create a unique key for same column name in two tables but different data in QlikView

How to create a unique key for same column name in two tables but different data

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Consider the following Employee table and the Data source ( Excel )

Emp_id	Emp_Name	Designation
101	Sharmila	Developer
201	Rency	Testing
311	Rita	Testing

DataSource MSAccess Database:

Emp_id	Employee Name	Status
1	Sharmila Devi Venkatesan	Regular
2	Rency.M	Regular
3	Rita John	Contract

My problem here is I have same column(Emp_id) in two tables in different sources but I need to combine these two tables. And I dont have a unique field in both tables expect Emp_id,Employee Name but it has different data inside it.How can I combine these two tables by forming a unique key?

Re: How to create a unique key for same column name in two tables but different data

bgerchikov — Thu, 15 Jun 2017 20:13:42 GMT

Hi Sharmila!

You must have mapping table and use ApplyMap function to join the data:

MapEmpID:

Mapping Load

Emp_id As Source1

To As Source2

From ....

Join(Table1)

Load

Applymap('MapEmpID', Emp_id) as Emp_id,

Status

resident Table2

;

Thanks!

Re: How to create a unique key for same column name in two tables but different data

Thu, 15 Jun 2017 20:58:29 GMT

No it is not working.

Re: How to create a unique key for same column name in two tables but different data

MarcoWedel — Thu, 15 Jun 2017 21:42:59 GMT

please describe your expected result.

thanks

regards

Marco

Re: How to create a unique key for same column name in two tables but different data

rwunderlich — Thu, 15 Jun 2017 21:44:58 GMT

Is there some pattern that maps the values to each other? In your sample you show 101 = 1. Is it as simple as taking the first digit or is the data more random than that? Do you need to match on the employee name?

-Rob

Re: How to create a unique key for same column name in two tables but different data

Thu, 15 Jun 2017 21:53:16 GMT

My expected result should be like below:

Emp_id	Emp_Name	Designation	Status
101_1	Sharmila	Developer	Regular
201_2	Rency	Testing	Regular
311_3	Rita	Testing	Contract

Both tables have the same employee record but different Emp_id is maintained for both. And also in one Data source Full name of an employee is stored in other source only the First Name is stored. So combining both Emp_id I need to create a unique Emp_id column.How can we make it?

Re: How to create a unique key for same column name in two tables but different data

Thu, 15 Jun 2017 21:56:38 GMT

No it is not a pattern rather it is a random digit with different values. No even employee name is maintained differently one with FullName and other with only FirstName.

Re: How to create a unique key for same column name in two tables but different data

rwunderlich — Thu, 15 Jun 2017 22:02:21 GMT

Can you describe what rules or algorithm you would use to associate the two rows together?

-Rob

Re: How to create a unique key for same column name in two tables but different data

Saravanan_Desingh — Thu, 15 Jun 2017 22:25:08 GMT

Can you check this?

Table1:

LOAD Emp_id As Emp1,

Capitalize(Emp_Name) As Fname,

Designation

FROM

[https://community.qlik.com/thread/264276]

(html, codepage is 1252, embedded labels, table is @1);

Left Join

Table2:

LOAD Emp_id As Emp2,

Capitalize(SubField(SubField([Employee Name],' ',1),'.',1)) As Fname,

Status

FROM

[https://community.qlik.com/thread/264276]

(html, codepage is 1252, embedded labels, table is @2);