Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
I don't understand how you retrieve
125 | e | xx |
in your resulting table, since surname xx doesn't exist in your input tables.
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;