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

[#1]

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?

[bgerchikov]

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!

[#3]

No it is not working.

[MarcoWedel]

please describe your expected result.

thanks

regards

Marco

[rwunderlich]

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

[#6]

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?

[#7]

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.

[rwunderlich]

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

-Rob

[Saravanan_Desingh]

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);