topic Re: Outer Join without duplicates in QlikView

Outer Join without duplicates

lbunnell — Thu, 29 Sep 2016 14:00:18 GMT

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 Number	Old Loan Number
123455	678901
123455	-
123456	-
123457	123051
123457	-

Re: Outer Join without duplicates

Ricardo_Gerhard — Thu, 29 Sep 2016 14:10:50 GMT

Dear Lawrence,

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

Re: Outer Join without duplicates

lbunnell — Thu, 29 Sep 2016 14:13:18 GMT

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

marcus_sommer — Thu, 29 Sep 2016 14:26:57 GMT

You could try something like this:

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

concatenate(t)

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

- Marcus

Re: Outer Join without duplicates

Ricardo_Gerhard — Thu, 29 Sep 2016 14:29:01 GMT

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.

Re: Outer Join without duplicates

lbunnell — Thu, 29 Sep 2016 16:03:12 GMT

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.

Re: Outer Join without duplicates

lbunnell — Thu, 29 Sep 2016 16:27:22 GMT

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

marcus_sommer — Fri, 30 Sep 2016 05:54:30 GMT

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

Re: Outer Join without duplicates

lbunnell — Fri, 30 Sep 2016 12:54:09 GMT

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

Re: Outer Join without duplicates

teiswamsler — Fri, 30 Sep 2016 13:10:11 GMT

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

Re: Outer Join without duplicates

lbunnell — Fri, 30 Sep 2016 14:38:14 GMT

Sorry, I see that wasn't as clear as it needed to be. Need to keep ALL records from both tables, but match where there is a match between Loan Number and New Loan Number. The problem is that not every Old Loan Number has a New Loan Number, so matching on Loan Number and New Loan Number alone loses records. Again trying to keep track of 1) all loans in current inventory, 2) all those that have paid off and 3) those that have been paid off and customer recaptured as new loan.