Need help with Sum..Group by

[#1]

I have two tables right now, one of them is like this:

ID	Qnt
1	5
2	6
3	7

And the other is like this:

ID	Code
1	A
2	B
3	B

I need to sum Qnt grouping by code (B would be 13 for example, since 6 and 7 are part of B), but when I try to do this, while I get the right numbers on B, A is returning me 10 instead of 5.

This is the script that I'm using:

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

left join

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

Final_Table

LOAD

    Code

    Sum(Qnt) as Result

Resident Temp_Table Group By Code;

Drop Table Temp_Table;

What am I doing wrong? Thank you in advance for the help.

[trdandamudi]

You need to drop the Temp_Table...

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

left join

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

Final_Table:

LOAD

    Code,

    Sum(Qnt) as Result

Resident Temp_Table Group By Code;

Drop Table Temp_Table;

[#3]

I forgot to mention I've dropped the first table after the sum, it does sum anyway though. What else could it be?

Thank you

[maxgro]

try without join (add a chart with Code and sum(Qnt))

what's the output?

Temp_Table:

LOAD ID,

          Qnt,

FROM Table_A.xlsx (ooxml, no labels);

Temp_Table1:

LOAD ID,

          Code

FROM Table_B.xlsx (ooxml, embedded labels);

[trdandamudi]

I am getting the correct results as you expected. Please take a look at the attached file..

[#6]

I can't sum since they are on different tables now,

Field not found <Code>.

Any clue?

Thank you

[#7]

I can't open, I'm a free user. Could you please post the script?

[maxgro]

I said in a chart. Code is a field of one of your source excel.

Anyway, could you post the excel?

Maybe you have some duplicated ID in both the excel.

[trdandamudi]

Here is the code:

Temp_Table:

Load * Inline [

ID,Qnt

1,5

2,6

3,7

];

Left join (Temp_Table)

Load * Inline [

ID, Code

1,A

2,B

3,B

];

Final_Table:

Load

  Code,

  Sum(Qnt) as Result

Resident Temp_Table

Group By Code;

Drop Table Temp_Table;