Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hey there,
Have you tried to join with concatenate?
Best regards,
D.A. MB
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..
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
hi
try to create link table .
$@M.
May be check this link out:
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
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);
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!