topic Need help with Sum..Group by in QlikView

Need help with Sum..Group by

Fri, 29 Jul 2016 19:04:07 GMT

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.

Re: Need help with Sum..Group by

trdandamudi — Fri, 29 Jul 2016 19:41:10 GMT

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;

Re: Need help with Sum..Group by

Fri, 29 Jul 2016 19:43:03 GMT

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

Re: Need help with Sum..Group by

maxgro — Fri, 29 Jul 2016 20:13:33 GMT

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

Re: Need help with Sum..Group by

trdandamudi — Fri, 29 Jul 2016 20:19:18 GMT

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

Re: Need help with Sum..Group by

Fri, 29 Jul 2016 20:20:25 GMT

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

Field not found <Code>.

Any clue?

Thank you

Re: Need help with Sum..Group by

Fri, 29 Jul 2016 20:21:15 GMT

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

Re: Need help with Sum..Group by

maxgro — Fri, 29 Jul 2016 20:45:06 GMT

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.

Re: Need help with Sum..Group by

trdandamudi — Fri, 29 Jul 2016 20:53:02 GMT

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;