Mapping data

[#1]

Hi Experts,

I have attached a sample qvw which has employee details from two different tables.

The problem here is for one Employee ID, there are two records.

I want to make it as one with all the required data.

i have attached the excel sheet which shows the required output.

Please help me to do this.

[sunny_talwar]

I don't see why you need this:

TABLE1:

LOAD [First Name],

     [Last Name],

     [Employee ID],

     [Email Address],

     [Mobile No.],

     Cisco,

     [IP Phone]

FROM

Test_EM1.xls

(biff, embedded labels, table is Sheet1$);

Everything in this table is already available in TABLE2:

TABLE2:

LOAD [First Name],

     [Last Name],

     [Employee ID],

     [Email Address],

     Department,

     Location,

     [Mobile No.],

     Cisco,

     [IP Phone],

     [Job Title],

     [Report to (Emp ID)]

FROM

Test_EM.xls

(biff, embedded labels, table is Sheet1$);

UPDATE: But if in your real scenario there are more columns which don't match, I would let the two tables join on Employee ID and comment the other common fields from TABLE1 with the hope that the values in TABLE2 will always match with those in TABLE1

[tresesco]

Try it like:

TABLE1:

LOAD [First Name],

     [Last Name],

    [Employee ID],

     [Email Address],

     [Mobile No.],

     Cisco,

     [IP Phone]

FROM

Test_EM1.xls

(biff, embedded labels, table is Sheet1$);

Left Join

TABLE2:              // Remove the fields those are already there in first table except [Employee ID]

LOAD

     [Employee ID],

     Department,

     Location,

     [Job Title],

     [Report to (Emp ID)]

FROM

Test_EM.xls

(biff, embedded labels, table is Sheet1$);

[Chanty4u]

is this

[#5]

Sunny,

These attachments are the source for this qvw.

Both have some common fields but one table will have some field values & other have other field values.

i have mentioned about one employee but my original data has 4000 records in one table & 9000 records in another table.

Some employee IDs will be common & some are not.

for the common employee IDs, i need to get all the required data from two tables & display as single record.

Please help

[#6]

thanks

but this will not give required output.

it doesnt have all the data

[tresesco]

Have you tried the code suggested? 

[Anonymous]

Do not add duplicate fields in both tables  and use EmployeeID as Key between two tables as  suggested by Sunny

[sunny_talwar]

Would you be able to add another row of data in both your Excels where employee IDs are not the same. and what would be the expected output in that case?

[#10]

Sunny,

i have attached the qvw and the source files with another employee that is not common