Merge Data and then join to another table

[#1]

Good morning too all,

how can I resolve the following problem:

content Table 1:

• ID
• Location
• etc.

content Table 2:

• ID
• freight cost
• freight cost type

Nearly every ID in Table2 is duplicated because it has two different freight cost types.
Now I want to have a sum of both and then left join to the table 1. When I just do the left join then I have also the duplications in Table 1, which I dont want to have.

Who know how to solve this?r

I appreciate every kind of help!

Thanks and regards

Martin

[tresesco · Accepted solution]

Try like:

Load

          ID,

          Location

From <Table1>;

Join

Load

          ID,

          Sum([Freight Cost]) as [Freight Cost]

From <Table2> Group By ID;

[its_anandrjs]

You can try like

1.Without joining them and leave with association

Load

          ID,

          Location

From Table1;

Load DISTINCT

          ID,

          freight cost type

          [Freight Cost]

From Table2 Group By ID;

2.

Load

          ID,

          Location

From Table1;

Join

Load 

          ID,

          freight cost type,

          Sum([Freight Cost]) as [Freight Cost]

From Table2 Group By ID,freight cost type;

[#4]

LOAD

Sum([Freight Cost]) as [Freight Cost],

[ÌD] as

FROM

(ooxml, embedded labels, header is 1 lines, table is Fracht);

where I have to add the GROUP BY function? And do I have to say Group By ID or Group By Code cause I renamed it?

[its_anandrjs]

You have to write like

LOAD

Sum([Freight Cost]) as [Freight Cost],

[ÌD] as [Code]

FROM

(ooxml, embedded labels, header is 1 lines, table is Fracht)

Group By [ÌD];

If there is only one dimension then try like this way.

[#6]

THANKS YOU ARE THE BEST !