topic Re: Mapping data in QlikView

Mapping data

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Mapping data

sunny_talwar — Thu, 24 Mar 2016 11:13:46 GMT

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

Re: Mapping data

tresB — Thu, 24 Mar 2016 11:14:30 GMT

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

Re: Mapping data

Chanty4u — Thu, 24 Mar 2016 11:17:01 GMT

is this

Re: Mapping data

Thu, 24 Mar 2016 11:23:27 GMT

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

Re: Mapping data

Thu, 24 Mar 2016 11:26:46 GMT

thanks

but this will not give required output.

it doesnt have all the data

Re: Mapping data

tresB — Thu, 24 Mar 2016 11:43:16 GMT

Have you tried the code suggested?

Re: Mapping data

Anonymous — Thu, 24 Mar 2016 11:49:10 GMT

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

Re: Mapping data

sunny_talwar — Thu, 24 Mar 2016 12:02:33 GMT

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?

Re: Mapping data

Thu, 24 Mar 2016 12:13:34 GMT

Sunny,

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

Re: Mapping data

Thu, 24 Mar 2016 12:15:03 GMT

Hi,

i didnt add duplicates.

Source was given like that. i cant remove duplicates since i need both.

The required fields are there in both the tables

Re: Mapping data

Thu, 24 Mar 2016 12:19:58 GMT

I would suggest creating a QVD for Emp data and one for a list of Emp that have Reports to.

this would allow you to do the one row per emp join to one manager data line (both from the same qvd)

Re: Mapping data

tresB — Thu, 24 Mar 2016 12:29:04 GMT

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

right join

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

Re: Mapping data

sunny_talwar — Thu, 24 Mar 2016 12:32:38 GMT

May be this:

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

Concatenate (TABLE2)

LOAD [First Name],

[Last Name],

[Employee ID],

[Email Address] ,

[Mobile No.],

Cisco,

[IP Phone]

FROM

Test_EM1.xls

(biff, embedded labels, table is Sheet1$)

Where not Exists([Employee ID]);