topic Re: Complicated join logic in QlikView

Complicated join logic

MindaugasBacius — Tue, 08 Nov 2016 14:51:22 GMT

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!

Re: Complicated join logic

MindaugasBacius — Wed, 09 Nov 2016 06:12:40 GMT

Does anyone have any ideas on this matter?

Re: Complicated join logic

its_anandrjs — Wed, 09 Nov 2016 06:39:00 GMT

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

Re: Complicated join logic

tamilarasu — Wed, 09 Nov 2016 06:43:44 GMT

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;

Re: Complicated join logic

Gysbert_Wassenaar — Wed, 09 Nov 2016 07:03:07 GMT

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

;