Joining Tables & removing duplicates

[#1]

Dear Community,

I'm trying to join 2 tables together. My first table has a unique field. My second table is a concatenation of multiple tables and so depending where the system comes from, the fields may be more or less populated. What i need is something where i can join the two tables where i tell Qlik which field entry should be "prioritised"

For example

Table 1:

Unique	Field 2
AAB	Blah 1
AAC	Blah 2
AAD	Blah 3

Table 2:

Unique	System	Field 3	Field 4
AAB	A	23
AAB	B	23
AAB	C	24	ZZA
AAC	B	20	ZZB
AAC	C	20
AAD	A	18	ZZC

I'm looking for a join statement that says, if data exists in "system A" then use that data. If the cell is Null then pick the data from "System B". If this is Null then pick the data from "System C" - This should be applicable to all Fields.....

I would then drop the field "system"

expected resulting table would be:

Unique	Field 2	Field 3	Field 4
AAB	Blah 1	23	ZZA
AAC	Blah 2	20	ZZB
AAD	Blah 3	18	ZZC

Many thanks!

[hic · Accepted solution]

I would keep the two tables as two tables, but transform table 2 in the following way:

tmpData:
Load * From <Source>;

Data:
Load
RecNo() as LineID,
[Unique],
[System],
If(Len(Trim(Peek([Field 3])))>0 and [Unique]=Peek([Unique]),Peek([Field 3]),[Field 3]) as [Field 3],
If(Len(Trim(Peek([Field 4])))>0 and [Unique]=Peek([Unique]),Peek([Field 4]),[Field 4]) as [Field 4]
Resident tmpData Order By Unique, System ;

Inner Join (Data)
Load [Unique], Max(LineID) as LineID
Resident Data Group By [Unique];

Drop Table tmpData;
Drop Fields System, LineID;

HIC