Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with Sum..Group by

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

IDQnt
15
26
3

7

And the other is like this:

ID

Code

1

A

2

B
3B

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.

8 Replies
trdandamudi
Master II
Master II

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;

Not applicable
Author

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
MVP
MVP

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
Master II
Master II

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

Not applicable
Author

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

Field not found <Code>.

Any clue?

Thank you

Not applicable
Author

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

maxgro
MVP
MVP

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
Master II
Master II

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;