topic Merging Tables and missing info in QlikView

Merging Tables and missing info

Tue, 01 Mar 2016 16:50:40 GMT

Hi, I have a question as I still am not very familiar with the coding in Qlikview.

Table 1:

employee ID	Name	Surname
123	a	b
124	c
125	e	f

Table 2:

Employee ID	Name	Surname
123	a	b
124	c	d
126	g	h

What I need is to get the following:

Employee ID	Name	Surname
123	a	b
124	c	d
125	e	xx
126	g	h

So effectively it has added the Surname to employee ID 124 where it was missing and added employee ID 126 and all fields.

I have tried to load the data as follows:

Table1:

Load

[Employee ID],

Name,

Surname

From File1.xls

Left Join (Table)

Load

[Employee ID],

Name,

Surname

From File2.xls

This code will also over ride name 125 with the Surname xx. All I want to do is use data that is in table 2 where it is missing in table 1.

Much appreciate any help people can give me to point me in the correct direction.

regards,

Roberto

Re: Merging Tables and missing info

swuehl — Tue, 01 Mar 2016 18:33:26 GMT

I don't understand how you retrieve

125

in your resulting table, since surname xx doesn't exist in your input tables.

Re: Merging Tables and missing info

maxgro — Tue, 01 Mar 2016 18:56:03 GMT

maybe

Table1:

load [employee ID], MaxString(Name) as Name, MaxString(Surname) as Surname

inline [

employee ID, Name, Surname

123,a, b

124,c,

125,e,f

]

group by [employee ID]

;

join (Table1)

load [employee ID], MaxString(Name) as Name2, MaxString(Surname) as Surname2 inline [

employee ID, Name, Surname

123, a, b

124, c, d

126, g, h

]

group by [employee ID]

;

Final:

NoConcatenate load

[employee ID],

if(len(trim(Surname))=0 or len(trim(Name))=0, Surname2, Surname) as Surname,

if(len(trim(Surname))=0 or len(trim(Name))=0, Name2, Name) as Name

Resident Table1;

DROP Table Table1;