topic Re: Joining with 2 different keys. in App Development

Joining with 2 different keys.

Correct_Answer — Thu, 07 Oct 2021 07:58:04 GMT

Hi Team,

I have requirement like to join two different tables with two incomplete keys.

for Example:

I have 2 data sets

data set1 :

Sales Order ID	Sales Number
SO61145	1
SO61570	2
SO61892	3
SO61953
SO62004
SO62095	4
SO62245	5
SO62485
SO62601

Sales order id and sales number are the two different keys in data set 1 some records mapping with sales order id form data set 1 to Dataset 2 and some records are not...

In order to complete the mapping me need to join two data sets with both the keys .

Data Set 2:

Customer	Sales Order ID	Sales Reference	ShipDate	Product	Sales	Quantity
Curtis, Liu		1	1/7/2013	All-Purpose Bike Stand	159.00	1
Josue, Blanco		2	1/13/2013	All-Purpose Bike Stand	159.00	1
Kellie, Vazquez		3	1/18/2013	All-Purpose Bike Stand	159.00	1
Julian, Flores	SO61953		1/19/2013	All-Purpose Bike Stand	159.00	1
Taylor, Taylor	SO62004		1/20/2013	All-Purpose Bike Stand	159.00	1
Jill, Hernandez		4	1/22/2013	All-Purpose Bike Stand	159.00	1
Gina, Martin		5	1/24/2013	All-Purpose Bike Stand	159.00	1
Andrea, Morris	SO62485		1/28/2013	All-Purpose Bike Stand	159.00	1
Samuel, Ross	SO62601		1/30/2013	All-Purpose Bike Stand	159.00	1
Troy, Mehta	SO62654		1/31/2013	All-Purpose Bike Stand	159.00	1
Dominic, Madan	SO62727		2/1/2013	All-Purpose Bike Stand	159.00	1
Colin, He	SO62816		2/2/2013	All-Purpose Bike Stand	159.00	1
Brad, Oliver	SO62776		2/2/2013	All-Purpose Bike Stand	159.00	1
Natalie, Diaz	SO62924		2/4/2013	All-Purpose Bike Stand	159.00	1
Roger, Harui	SO63036		2/6/2013	All-Purpose Bike Stand	159.00	1
Hailey, Morgan	SO63054		2/6/2013	All-Purpose Bike Stand	159.00	1
Corey, Lal	SO63059		2/6/2013	All-Purpose Bike Stand	159.00	1
Jasmine, Taylor	SO63329		2/8/2013	All-Purpose Bike Stand	159.00	1
Erika, Dominguez	SO63386		2/9/2013	All-Purpose Bike Stand	159.00	1

Can any one help me to join these two keys without any duplicate values and output table will be as follows.

Customer	Sales Order ID	Contract Reference Number	ShipDate	Product	Sales	Quantity
Curtis, Liu	SO61145	1	1/7/2013	All-Purpose Bike Stand	159.00	1
Josue, Blanco	SO61570	2	1/13/2013	All-Purpose Bike Stand	159.00	1
Kellie, Vazquez	SO61892	3	1/18/2013	All-Purpose Bike Stand	159.00	1
Julian, Flores	SO61953		1/19/2013	All-Purpose Bike Stand	159.00	1
Taylor, Taylor	SO62004		1/20/2013	All-Purpose Bike Stand	159.00	1
Jill, Hernandez	SO62095	4	1/22/2013	All-Purpose Bike Stand	159.00	1
Gina, Martin	SO62245	5	1/24/2013	All-Purpose Bike Stand	159.00	1
Andrea, Morris	SO62485		1/28/2013	All-Purpose Bike Stand	159.00	1
Samuel, Ross	SO62601		1/30/2013	All-Purpose Bike Stand	159.00	1

Thanks In advance.

Re: Joining with 2 different keys.

Or — Thu, 07 Oct 2021 08:27:03 GMT

Assuming it's always one or the other, which appears to be the case here, just create a simple key with an if() statement:

Load *, if(len([Sales Number])=0,[Sales Order ID],[Sales Number]) as Key INLINE [
Sales Order ID, Sales Number
SO61145, 1
SO61570, 2
SO61892, 3
SO61953,
SO62004,
SO62095, 4
SO62245, 5
SO62485,
SO62601, ];

Left join
[Table]:
LOAD if(len([Sales Reference])=0,[Sales Order ID],[Sales Reference]) as Key, Customer, ShipDate,Product,Sales,Quantity INLINE
[
Customer;Sales Order ID;Sales Reference;ShipDate;Product;Sales;Quantity
Curtis, Liu; ;1;1/7/2013;All-Purpose Bike Stand;159.00;1
Josue, Blanco; ;2;1/13/2013;All-Purpose Bike Stand;159.00;1
Kellie, Vazquez; ;3;1/18/2013;All-Purpose Bike Stand;159.00;1
Julian, Flores;SO61953; ;1/19/2013;All-Purpose Bike Stand;159.00;1
Taylor, Taylor;SO62004; ;1/20/2013;All-Purpose Bike Stand;159.00;1
Jill, Hernandez; ;4;1/22/2013;All-Purpose Bike Stand;159.00;1
Gina, Martin; ;5;1/24/2013;All-Purpose Bike Stand;159.00;1
Andrea, Morris;SO62485; ;1/28/2013;All-Purpose Bike Stand;159.00;1
Samuel, Ross;SO62601; ;1/30/2013;All-Purpose Bike Stand;159.00;1
Troy, Mehta;SO62654; ;1/31/2013;All-Purpose Bike Stand;159.00;1
Dominic, Madan;SO62727; ;2/1/2013;All-Purpose Bike Stand;159.00;1
Colin, He;SO62816; ;2/2/2013;All-Purpose Bike Stand;159.00;1
Brad, Oliver;SO62776; ;2/2/2013;All-Purpose Bike Stand;159.00;1
Natalie, Diaz;SO62924; ;2/4/2013;All-Purpose Bike Stand;159.00;1
Roger, Harui;SO63036; ;2/6/2013;All-Purpose Bike Stand;159.00;1
Hailey, Morgan;SO63054; ;2/6/2013;All-Purpose Bike Stand;159.00;1
Corey, Lal;SO63059; ;2/6/2013;All-Purpose Bike Stand;159.00;1
Jasmine, Taylor;SO63329; ;2/8/2013;All-Purpose Bike Stand;159.00;1
Erika, Dominguez;SO63386; ;2/9/2013;All-Purpose Bike Stand;159.00;1
](delimiter is ';');

Re: Joining with 2 different keys.

abhijitnalekar — Thu, 07 Oct 2021 08:30:34 GMT

You can create a composite key of both columns

like Sales Order ID&Contract Reference Number for dataset 2 and

Sales Order ID&salesnumber Number

Re: Joining with 2 different keys.

abhijitnalekar — Thu, 07 Oct 2021 08:33:02 GMT

Create Script as below

LOAD
"Sales Order ID"&"Sales Number" as key,
"Sales Number"
FROM [lib://desk/Sales Orders_source1.xlsx]
(ooxml, embedded labels, table is DS1);

LOAD
Customer,
"Sales Order ID" & "Sales Reference" as key,
"Sales Reference",
ShipDate,
Product,
Sales,
Quantity,
F8
FROM [lib://desk/Sales Orders_source1.xlsx]
(ooxml, embedded labels, table is DS2);