Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | - |
Dear Lawrence,
Can you provide a simple table with a sample result table?
What I'm getting is like the table above. What I'd like to see is the table above without the duplicate loan numbers.
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
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.
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.
Meant to say 1 record for each record in either table - [Loan Number] and [Old Loan Number] and matched where [Loan Number]=[Loan Number]
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
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
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