join

[raadwiptec]

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.

[miguelbraga]

Hey there,

Have you tried to join with concatenate?

Best regards,

D.A. MB

[raadwiptec]

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

[miguelbraga]

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

[mightyqlikers]

hi

try to create link table .

$@M.

[sunny_talwar]

May be check this link out:

Don't join - use Applymap instead

[puttemans]

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

[susovan]

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);

[Anonymous]

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!