Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lbunnell
Creator
Creator

Outer Join without duplicates

I'm trying to join two tables and keep records from both. Some of the record id/loan numbers are in both tables but some are not. I'm ending up with duplicates using an outer join. Is there a way to prevent this?

Loan NumberOld Loan Number
123455678901
123455-
123456-
123457123051
123457-
10 Replies
Ricardo_Gerhard
Employee
Employee

Dear Lawrence,

   Can you provide a simple table with a sample result table?

Ricardo Gerhard
OEM Solution Architect
LATAM
lbunnell
Creator
Creator
Author

What I'm getting is like the table above. What I'd like to see is the table above without the duplicate loan numbers.

marcus_sommer

You could try something like this:

t:

load [Loan Number], [Old Loan Number] from Source1;

     concatenate(t)

load [Loan Number], [Old Loan Number] from Source2 where not exists([Loan Number]);

- Marcus

Ricardo_Gerhard
Employee
Employee

Ok Lawrence,

   There´re many ways to fix this situations. Below some methods:

- Using isnull on load script; IsNull ‒ QlikView

- Reading on a separate table and performing a update/fix dates using "where" clause;

- Using IF and IsNull on his data object correcting the display.

   Please, mark the CORRECT/HELPFULL comments.

Ricardo Gerhard
OEM Solution Architect
LATAM
lbunnell
Creator
Creator
Author

More clarification may be needed. The 1st table is a record of all current loans (no [Old Loan Number] records exists in this table). The 2nd table contains loans paid off [Old Loan Number] as well as those where the customer was recaptured with a new loan (indicated by [Loan Number] of the new loan). The 2nd table new [Loan Number] may be contained in the 1st table, but may not due to timing issues. I want to keep one record for each [Loan Number] that exists in either table. Hope that helps.

lbunnell
Creator
Creator
Author

Meant to say 1 record for each record in either table - [Loan Number] and [Old Loan Number] and matched where [Loan Number]=[Loan Number]

marcus_sommer

For me isn't it quite clear yet how your sources look like and what should be the expected result by merging them. It could be that it won't be possible with an one-step solution - maybe you need further load steps to flag and remove the unwanted records, for example with a check to the previous record in a sorted load. Take a look here what I mean: Peek() or Previous() ?

- Marcus

lbunnell
Creator
Creator
Author

I believe you are right about a 2 step process. So table 1 will have fields

Loan Number

Customer Name

Address

etc

Table 2 will have fields

New Loan Number

Old Loan Number

Customer Name

Address

etc

When I perform a simple outer join I get the results in the initial post in this thread. I'd like to include all of the records from the 1st table all of the records with an Old Loan Number from the second table, matching on any records where the Loan Number = New Loan Number

teiswamsler
Partner - Creator III
Partner - Creator III

Hi Lawrence

It seems like you just wanna join the new Loan Number on existing data?

Load table2 as a mapping table

map_newLoanNumber:

Mapping

Load

     Old Loan Number,

     New Loan Number

From X;

Then use the applymap funktion on table1

Table1:

Load

     *,

     applymap('map_newLoanNumber', [Loan Number], 'N/A' ) as [New Loan Number]

From Y;

/Teis