Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

lbunnell
Contributor

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
Employee
Employee

Re: Outer Join without duplicates

Dear Lawrence,

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

Ricardo Gerhard
OEM Solution Architect
LATAM
lbunnell
Contributor

Re: Outer Join without duplicates

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

Re: Outer Join without duplicates

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

Employee
Employee

Re: Outer Join without duplicates

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
Contributor

Re: Outer Join without duplicates

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
Contributor

Re: Outer Join without duplicates

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

Re: Outer Join without duplicates

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
Contributor

Re: Outer Join without duplicates

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
Contributor II

Re: Outer Join without duplicates

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

Community Browser