I have two tables that I need to join in the data load.
Table 1
Phone Number
Customer
555-555-5555
Customer Name
444-444-4444
Customer Name
---
Table 2
Phone Number
Customer Name
111-111-1111
N/A
555-555-5555
N/A
---
I need to join these two data tables into one, and if the phone number in Table 2 exists in Table 1, then show the number and the associated customer name. If the phone number doesn't exist in Table 1, add the phone number and then show N/A for the Customer Name
Customer:
Load * Inline [
Phone Number, Customer
555-555-5555, Customer One
444-444-4444, Customer Two
];
Join(Customer) //This will be a full outer join
Load * Inline [
Phone Number, Customer Name
111-111-1111,N/A
555-555-5555,N/A
];
Left Join(Customer)
Load
[Phone Number],
Coalesce(Customer,[Customer Name]) as [Customer Desc]
Resident Customer;
Drop Fields Customer, Customer Name from Customer;
/* Result
//Keep in mind join conditions are equality only and always //based on common field names between the "named" data set and //the load statement "left join(NamedDataset) Load * ..."
Phone Number, Customer Desc
555-555-5555,Customer One
444-444-4444,Customer Two
111-111-1111,N/A
*/
