Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Joining Tables & removing duplicates

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:

UniqueField 2

AAB

Blah 1
AACBlah 2
AADBlah 3

Table 2:

UniqueSystemField 3Field 4
AABA23
AABB23
AABC24ZZA
AACB20ZZB
AACC20
AADA18ZZC

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:

UniqueField 2Field 3Field 4
AABBlah 123ZZA
AACBlah 220ZZB
AADBlah 318ZZC

Many thanks!

1 Solution

Accepted Solutions
hic
Employee
Employee

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

View solution in original post

1 Reply
hic
Employee
Employee

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