Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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
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);
I am getting the correct results as you expected. Please take a look at the attached file..
I can't sum since they are on different tables now,
Field not found <Code>.
Any clue?
Thank you
I can't open, I'm a free user. Could you please post the script?
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.
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;