Join 2 tables with conditions

[steve1982]

Dear Community,

I have 2 tables and that  I want to connect them with a condition join

For example:

Table A:

MPN	CUSTOMER	Commit ($)	Start Yr	End Yr	Agreement Date
S25F890A	ABC	$10,000	2018	2022	16-Aug-17
S278900B	ABC	$20,000	2018	2022	16-Aug-17
S234568A9	ABD	$30,000	2018	2022	16-Aug-17

Table B:

MPN	CUSTOMER	Sales ($)
S25F890A1	ABC	$35,000
S25F890A2	ABC	$10,000
S278900B1	ABC	$5,000
S278900B2	ABC	$1,000
S234568A9	ABD	$8,000

When the MPN in Table B contains MPN in Table A and the Customer in both tables matched then sum the Sales ($) value and join with Table A. When the MPN and customer are exactly the same then join the Sales ($) with Table A.

Final Output:

MPN	CUSTOMER	Commit ($)	Start Yr	End Yr	Agreement Date	Sales ($)
S25F890A	ABC	$10,000	2018	2022	16-Aug-17	$45,000
S278900B	ABC	$20,000	2018	2022	16-Aug-17	$6,000
S234568A9	ABD	$30,000	2018	2022	16-Aug-17	$8,000

Thanks

[Mark_Little]

Hi,

Could you please supply some more logic?

it will be possible with some form of Aggregate table and Join, but at the moment i can't see how it would work as the MPNs don't seem to match up between the tables and your final table.

Mark

[YoussefBelloum]

Hi,

you need to create a composite key, using MPN and CUSTOMERS fields

something like this:

tableA:

LOAD MPN as MPN_table1, CUSTOMER as CUSTOMER_table1, COMMIT, left(MPN,8)&CUSTOMER as KEY Inline [

MPN, CUSTOMER, COMMIT

S25F890A, ABC, 10000

S278900B, ABC, 20000

S234568A9, ABD, 30000

];

tableB:

LOAD MPN as MPN_table2, CUSTOMER as CUSTOMER_table2, Sales,left(MPN,8)&CUSTOMER as KEY Inline [

MPN, CUSTOMER, Sales

S25F890A1, ABC, 35000

S25F890A2, ABC, 10000

S278900B1, ABC, 5000

S278900B2, ABC, 1000

S234568A9, ABD, 8000

];

on the front end use a straight table, with MPN and Customer as dimensions and Sum(Sales) as measure

[steve1982]

Thanks for reply.

But the MPN will not has the fix length, it that anyway to do it without using Left(MPN,X)?

I am try use MapsubString and SubField, but not able to get the correct result.

Cust_MPN:

Mapping Load

MPN,

'_'&MPN&'_'&CUSTOMER as NEW_MPN

Resident Table A;

Final_Table:

Load * from Table A,

Left Join

Customer,

SubField(MapSubString('Cust_MPN', MPN), '_',2) as MPN,

Sales ($)

Resident Table B;

[YoussefBelloum]

what is the rule to join using the MPN field if it have not a fixe length ?

[steve1982]

The join logic is use the MPN & Customer.

If the MPN & Customer able to 100% match, then return the Sales ($).

Else the MPN (partially match) & match Customer, then return the Sales ($).

Green: 100% match with MPN & Customer

Yellow and Orange: Table B MPN contain Table A MPN with match customer