topic Re: join in QlikView

join

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

i have 3 tables that needs to be joined

table1:

Load

ID,

Group

Country,

Price

...

from xxxx

Where Match('Group','01','05','10')

table2:

Left Join (table1) Load

ID,

Country

Quantity

from xxxx

table3:

able2:

Left Join (table1) Load

ID,

Country

[Purchase Quantity]

from xxxx

My output should be as such.....below

ID | Country | Price | Quantity | [Purchase Quantity]

My issue is when joining 2 tables i get the correct price and qunatity values. But when Iam doing 3rd left join.. My price and quantity is doubling.

Iam left joining because i need the ID's only matching from Table 1.

suggest here please.

Re: join

miguelbraga — Tue, 27 Sep 2016 10:07:17 GMT

Hey there,

Have you tried to join with concatenate?

Best regards,

D.A. MB

Re: join

raadwiptec — Tue, 27 Sep 2016 11:05:37 GMT

hi migueal,

Concatenate considers al IDS' from all the tables which should not be in mycase.

I need only the matching ID's from table 1 to tanble 2 and table 3..

Re: join

miguelbraga — Tue, 27 Sep 2016 11:11:51 GMT

Hey there,

You've called the same table name in table 2 and table 3. You can simply call the ID and correspondent value of [Purchase Quantity] in a Mapping LOAD and then use the ApplyMap function to link to your Table1 (table 1 + table 2).

Hope this info helps you

Best regards,

D.A. MB

Re: join

mightyqlikers — Tue, 27 Sep 2016 11:12:54 GMT

try to create link table .

$@M.

Re: join

sunny_talwar — Tue, 27 Sep 2016 12:19:04 GMT

May be check this link out:

Don't join - use Applymap instead

Re: join

puttemans — Tue, 27 Sep 2016 12:44:52 GMT

Hi there,

Please try this:

Map_quantity:

Mapping load

ID&Country,

[Purchase Quantity]

from xxxx

table1:

Load

ID&Country as Link,

ID,

Group

Country,

Applymap('Map_quantity', ID&Country, '-') as Purchase_quantity

Price

...

from xxxx

Where Match('Group','01','05','10')

Left Join (table1)

Load

ID&Country as Link

Quantity

from xxxx

Re: join

susovan — Tue, 27 Sep 2016 12:45:14 GMT

Hi,

Try this process in your script.

Table1:

LOAD ID,

Group,

Country,

Price

FROM

Table1.xlsx

(ooxml, embedded labels, table is Sheet1) Where Group='g1';

Left Join

Table2:

LOAD ID,

Country,

Quantity

FROM

Table2.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join

Table3:

LOAD ID,

Country

FROM

Table3.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: join

Anonymous — Tue, 27 Sep 2016 13:31:56 GMT

Hi,

Try this:

table2:
Load
ID,
Country,
Quantity as QtyAux
from xxxx;

Concatenate (table2)
Load
ID,
Country,
[Purchase Quantity] as PyrchQtyAux
from xxxx;

FinalTable2:
LOAD
    ID,
    Country,
    Sum(QtyAux) as Quantity,
    Sum(PyrchQtyAux) as [Purchase Quantity]
RESIDENT table2
GROUP BY ID, Country;


table1:
Load
ID,
Group,
Country,
Price
from xxxx
Where Match('Group','01','05','10');

Left Join (table1)
LOAD
    ID,
    Country,
    Quantity,
    [Purchase Quantity]
resident FinalTable2;

DROP TABLE FinalTable2, table2;

Regards!