Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Mapping data

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.

Tags (1)
13 Replies
Highlighted

Re: Mapping data

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

Highlighted
MVP
MVP

Re: Mapping data

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

Highlighted
Chanty4u
Esteemed Contributor III

Re: Mapping data

is this

Highlighted
Not applicable

Re: Mapping data

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

Highlighted
Not applicable

Re: Mapping data

thanks

but this will not give required output.

it doesnt have all the data

Highlighted
MVP
MVP

Re: Mapping data

Have you tried the code suggested? 

Highlighted
rgvavihs
Valued Contributor

Re: Mapping data

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

Highlighted

Re: Mapping data

Capture.PNG

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?

Highlighted
Not applicable

Re: Mapping data

Sunny,

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