Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Complicated join logic

Hey,

There are main table.

Main:

ReceiverPayer
14
25
36

Two additional tables.

Table1

ReceiverDiscount
110
220

Table2

PayerDiscount
660

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

ReceiverPayerDiscount
1410
2520
3660

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!

1 Solution

Accepted Solutions
its_anandrjs

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

View solution in original post

4 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III
Author

Does anyone have any ideas on this matter?

its_anandrjs

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
Champion
Champion

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

;


talk is cheap, supply exceeds demand