Complicated join logic

[MindaugasBacius]

Hey,

There are main table.

Main:

Receiver	Payer
1	4
2	5
3	6

Two additional tables.

Table1

Receiver	Discount
1	10
2	20

Table2

Payer	Discount
6	60

I need to the most effective way to join two additional table to the Main one:

Receiver	Payer	Discount
1	4	10
2	5	20
3	6	60

I guess this might work but I need most intelligent way to deal with this issue:

Table:
Load
     Receiver
     ,Payer
From Main;

Left Join
     Receiver
     ,Discount
From Table1;

Left Join
     Payer
     ,Discount as Discount2
From Table2;

tmp:
Load Receiver
     ,Payer
     ,If(isnull(Discount), Discount2, Discount) as NewDiscount
Resident Table;

Drop Table Table;

Thank you!

[MindaugasBacius]

Does anyone have any ideas on this matter?

[its_anandrjs · Accepted solution]

Try this way by the mapping table

Table1:
Mapping
LOAD * Inline
[
Receiver,  Discount
1,  10
2,  20
];
 
Table2:
Mapping
LOAD * Inline
[
Payer,    Discount
6,  60
];

Main:
LOAD *,ApplyMap('Table1',Receiver,'') as Disc,
ApplyMap('Table2',Payer,'') as Disc2;
LOAD * Inline
[
Receiver,  Payer
1,  4
2,  5
3,  6
];
 
New:
LOAD Receiver&Payer as %Key,Receiver,Payer,Disc Resident Main;
Join(New)
LOAD Receiver&Payer as %Key,Receiver,Payer,Disc2 as Disc Resident Main;
DROP Table Main;

NoConcatenate
Final:
LOAD * Resident New Where Disc > 0;
DROP Table New;

Regards

Anand

[tamilarasu]

Hi Mindaugas,

I am not sure whether this is intelligent way or not. But thought to share with you and not tested.

Table_Receiver:
Mapping LOAD Receiver,
Discount
FROM Table1;

Table_Payer:
Mapping LOAD Payer,
Discount
FROM Table2;

Table:
LOAD Receiver,
Payer,
Applymap('Table_Receiver',Receiver,ApplyMap('Table_Payer',Payer,'No Discount Found')) as Discount
FROM
Main;

[Gysbert_Wassenaar]

Or perhaps like this:

Table_Receiver:
MAPPING LOAD

     Receiver,
     Discount
FROM

     Table1

     ;

Table_Payer:
MAPPING LOAD

     Payer,
     Discount
FROM

     Table2

     ;

Table:
LOAD

     Receiver,
     Payer,
     RangeSum(Applymap('Table_Receiver',Receiver,0),ApplyMap('Table_Payer',Payer,0)) as Discount
FROM
     Main

;